Filter only by Date using mongoDB c# driver

Question

I am using mongoDB c# latest driver i.e 3.+ in my project. I have different date filter criteria like Today,Last Day,Yesterday,This Month etc by using daterangepicker.

Here is my model

public class Student
    {
        public Student()
        {
        }
        [BsonId]
        [BsonRepresentation(BsonType.ObjectId)]
        public string Id { get; set; }
        [BsonDateTimeOptions(Kind = DateTimeKind.Local)]
        public DateTime CreatedOn { get; set; }
        [BsonDateTimeOptions(Kind = DateTimeKind.Local)]
        public DateTime ModifiedOn { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
    }

Here is the driver code

var server = new MongoClient(_connectionString);
var db = server.GetDatabase("Students");
var collection = db.GetCollection<Student>("student");
var filterBuilder = Builders<Student>.Filter;
var start = new DateTime(2017, 03, 29);
var end = new DateTime(2017, 03, 31);
var filter = filterBuilder.Gte(x => x.CreatedOn, new BsonDateTime(start)) &
             filterBuilder.Lte(x => x.CreatedOn, new BsonDateTime(end));
List<Student> searchResult = collection.Find(filter).ToList();

This code works fine but when I select todays filter then the date becomes

var start = new DateTime(2017, 03, 31);
var end = new DateTime(2017, 03, 31);

It didn't returns records for the current day . It is calculating time also.

I am saving dates as DateTime.Now. Sample ISO Date which I am querying are

"CreatedOn": ISODate("2017-03-31T20:27:12.914+05:00"),
"ModifiedOn": ISODate("2017-03-31T20:27:12.914+05:00"),

This is the dates filter I am using . Should I have to subract -1 from end dates ?

Need help what I am doing wrong.

Answer 1

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"} }} }");