Filter only by Date using mongoDB c# driver
I believe you are getting confused with time zones especially the offset part.
MongoDb always saves the date in UTC time.
So when you look at the date time in MongoDB you always have to factored in offset from your local time zone.
You'll always send the date in local time zone. Mongo C# driver changes time from local to UTC before persisting.
For example
When I save the document with CreatedOn = 2017-04-05 15:21:23.234 ( local time zone (America/Chicago) ) but
when you look at the documents in DB you will see something ISODate("2017-04-05T20:21:23.234Z") i.e local time offset from UTC which is -5 hours.
[BsonDateTimeOptions(Kind = DateTimeKind.Local)] indicates to driver to convert the time to local time from UTC when deserailsing the BSON back to your POCO.
Here is the test case explaining the behavior.
Code :
class Program
{
static void Main(string[] args)
{
var mongo = new MongoClient("mongodb://localhost:27017/test");
var db = mongo.GetDatabase("test");
db.DropCollection("students");
db.CreateCollection("students");
var collection = db.GetCollection<Student>("students");
var today = DateTime.Now; //2017-04-05 15:21:23.234
var yesterday = today.AddDays(-1);//2017-04-04 15:21:23.234
// Create 2 documents (yesterday & today)
collection.InsertMany(new[]
{
new Student{Description = "today", CreatedOn = today},
new Student{Description = "yesterday", CreatedOn = yesterday},
}
);
var filterBuilder1 = Builders<Student>.Filter;
var filter1 = filterBuilder1.Eq(x => x.CreatedOn, today);
List<Student> searchResult1 = collection.Find(filter1).ToList();
Console.Write(searchResult1.Count == 1);
var filterBuilder2 = Builders<Student>.Filter;
var filter2 = filterBuilder2.Eq(x => x.CreatedOn, yesterday);
List<Student> searchResult2 = collection.Find(filter2).ToList();
Console.Write(searchResult2.Count == 1);
}
}
public class Student
{
[BsonId]
[BsonRepresentation(BsonType.ObjectId)]
public string Id { get; set; }
[BsonDateTimeOptions(Kind = DateTimeKind.Local)]
public DateTime CreatedOn { get; set; }
public string Description { get; set; }
}
Collection : (when viewed through mongo shell)
{
"_id" : ObjectId("58e559c76d3a9d2cb0449d84"),
"CreatedOn" : ISODate("2017-04-04T20:21:23.234Z"),
"Description" : "yesterday"
}
{
"_id" : ObjectId("58e559c76d3a9d2cb0449d85"),
"CreatedOn" : ISODate("2017-04-05T20:21:23.234Z"),
"Description" : "today"
}
Update :
"CreatedOn": ISODate("2017-03-31T20:27:12.914+05:00")
The reason your comparison is not working is
var start = new DateTime(2017, 03, 31);
var end = new DateTime(2017, 03, 31);
This gets send to server as as $gte than ISODate("2017-03-31T00:00:00.000+05:00") and $lte than ISODate("2017-03-31T00:00:00.000+05:00") and it doesnt find the above entry.
The right way to query for today date will be
var start = new DateTime(2017, 03, 31);
var end = new DateTime(2017, 04, 01);
and update your filter to
var filter = filterBuilder.Gte(x => x.CreatedOn, start) &
filterBuilder.Lt(x => x.CreatedOn, end);
So now your range query is send to server as $gte than ISODate("2017-03-31T00:00:00.000+05:00") and $lt than ISODate("2017-04-01T00:00:00.000+05:00") and you should be able to find all matches for today.
Update 2
Change your database to store the date time with time part set to 00:00:00. This will remove the time part out of the equation from db too and your old range queries will work just fine for all cases.
Change your save method to use
var today = DateTime.Today; //2017-03-31 00:00:00.000
You can go back to old filter definition.
Something like
var start = new DateTime(2017, 03, 31);
var end = new DateTime(2017, 03, 31);
and update your filter to
var filter = filterBuilder.Gte(x => x.CreatedOn, start) &
filterBuilder.Lte(x => x.CreatedOn, end);
So now your range query is send to server as $gte than ISODate("2017-03-31T00:00:00.000+05:00") and $lte than ISODate("2017-03-31T00:00:00.000+05:00") and you should be able to find all matches for today.
Update 3 - Date only comparison using BsonDocument.
The idea here is to add timezone offset which is +5:00 to the server's UTC date and transform the calculated datetime to string yyyy-MM-dd format using $dateToSting operator followed by comparison on input string date in the same format.
This will work in your timezone but will not work in DST observing time zones.
Mongo Version 3.4
You can use $addFields stage which adds new field CreatedOnDatewhile keeping all the existing properties and last $project to drop the CreatedOnDate from the final response after comparison.
Shell Query:
{
"$addFields": {
"CreatedOnDate": {
"$dateToString": {
"format": "%Y-%m-%d",
"date": {
"$add": ["$CreatedOn", 18000000]
}
}
}
}
}, {
"$match": {
"CreatedOnDate": {
"$gte": "2017-03-31",
"$lte": "2017-03-31"
}
}
}, {
"$project": {
"CreatedOnDate": 0
}
}
C# code :
var start = new DateTime(2017, 03, 31);
var end = new DateTime(2017, 03, 31);
var addFields = BsonDocument.Parse("{$addFields: { CreatedOnDate: { $dateToString: { format: '%Y-%m-%d', date: {$add: ['$CreatedOn', 18000000] }} }} }");
var match = new BsonDocument("CreatedOnDate", new BsonDocument("$gte", start.ToString("yyyy-MM-dd")).Add("$lte", end.ToString("yyyy-MM-dd")));
var project = new BsonDocument
{
{ "CreatedOnDate", 0 }
};
var pipeline = collection.Aggregate().AppendStage<BsonDocument>(addFields)
.Match(match)
.Project(project);
var list = pipeline.ToList();
List<Student> searchResult = list.Select(doc => BsonSerializer.Deserialize<Student>(doc)).ToList();
Mongo Version = 3.2
Same as above but this pipeline uses $project so you'll have to add all the fields that you want to keep in final response.
Shell Query:
{
"$project": {
"CreatedOn": 1,
"Description": 1,
"CreatedOnDate": {
"$dateToString": {
"format": "%Y-%m-%d",
"date": {
"$add": ["$CreatedOn", 18000000]
}
}
}
}
}, {
"$match": {
"CreatedOnDate": {
"$gte": "2017-03-31",
"$lte": "2017-03-31"
}
}
}, {
"$project": {
"CreatedOn": 1,
"Description": 1
}
}
C# code:
var start = new DateTime(2017, 03, 31);
var end = new DateTime(2017, 03, 31);
var project1 = new BsonDocument
{
{ "CreatedOn", 1 },
{ "Description", 1 },
{ "CreatedOnDate", new BsonDocument("$dateToString", new BsonDocument("format", "%Y-%m-%d")
.Add("date", new BsonDocument("$add", new BsonArray(new object[] { "$CreatedOn", 5 * 60 * 60 * 1000 }))))
}
};
var match = new BsonDocument("CreatedOnDate", new BsonDocument("$gte", start.ToString("yyyy-MM-dd")).Add("$lte", end.ToString("yyyy-MM-dd")));
var project2 = new BsonDocument
{
{ "CreatedOn", 1 },
{ "Description", 1 }
};
var pipeline = collection.Aggregate()
.Project(project1)
.Match(match)
.Project(project2);
var list = pipeline.ToList();
List<Student> searchResult = list.Select(doc => BsonSerializer.Deserialize<Student>(doc)).ToList();
Update 4 - Date only comparison that works with day light savings.
Mongo Version = 3.6
Everything stays same expect $dateToString will take the timezone instead of fixed offset which should take care of day light saving changes into account.
Shell Update:
{
"$addFields": {
"CreatedOnDate": {
"$dateToString": {
"format": "%Y-%m-%d",
"date": "$CreatedOn",
"timezone": "America/New_York"
}
}
}
}
C# Update:
var addFields = BsonDocument.Parse("{$addFields: { CreatedOnDate: { $dateToString: { format: '%Y-%m-%d', date: "$CreatedOn", "timezone": "America/New_York"} }} }");