MongoDB - search by date and time using C# driver

Question

Hello I want to find entries between two dates (with time) using C# driver for MongoDB, but the Find + Filter method I use ignores the time and searches by date only (I think). What am I doing wrong?

My POCO:

public class TestClassForMongo
{
    public ObjectId Id { get; set; }
    public DateTime CreatedDateUtc { get; set; }
    public string Message { get; set; }
}

My search code:

IMongoCollection<TestClassForMongo> collection = db.GetCollection<TestClassForMongo>("mongoTest");

var filterBuilder = Builders<TestClassForMongo>.Filter;
var filter = filterBuilder.Gt("CreatedDateUtc", new DateTime(2016, 03, 04, 21, 0, 0)) &
             filterBuilder.Lt("CreatedDateUtc", new DateTime(2016, 03, 04, 22, 0, 0));
List<TestClassForMongo> searchResult = collection.Find(filter).ToList();

The above code returns empty array, although this:

collection.Find(filterBuilder.Empty).First().CreatedDateUtc

Returns the date: "2016-03-04 21:21:54"

MongoDB 3.2.3, C# MongoDB driver 2.2.3

The driver docs: https://docs.mongodb.org/getting-started/csharp/query/

THE ANSWER:

I did not give enough information for anyone to answer this question, the problem was timezone and UTC related issues, quite basic ones too. I used DateTime.UtcNow to store the date in the database. It is stored as "CreatedDateUtc" : ISODate("2016-03-04T21:21:54.836Z"). Getting this in C# returns a date which is actually a UTC date (the Kind property is UTC), which btw is indicated by the 'Z' suffix of the value in db. Comparing this UTC date with a new DateTime() makes not much sense, as the latter creates a date in your time zone, which can be different than +0 (UTC).

So one option would be to create the date for filter like this:

 new DateTime(2016, 03, 04, 21, 0, 0).ToUniversalTime()

Or modify the hour part to accomodate the timezone differences, in my example it would be adding 1 hour (as I am in +1 time zone).

So actually the time stored was 22:21:54 in my time zone. If I do a search between 22:00:00 and 23:00:00 using dates created in my time zone, I get the correct results.

Answer 1

Add BSON attribute on dateTime field (see below),

you can use linqu syntax to build such a query

    var min = new DateTime(2016, 03, 03, 22, 0, 0);
    var max = (new DateTime(2016, 03, 03, 23, 0, 0));
    List<TestClassForMongo> searchResult = collection.Find( 
                x => x.CreatedDateUtc > min &
                x.CreatedDateUtc < max
                ).ToList();

BSON ATTRIBUTE

public class TestClassForMongo
{
    public ObjectId Id { get; set; }

    [BsonDateTimeOptions]
    public DateTime CreatedDateUtc { get; set; }

    public string Message { get; set; }
}

linqPad dump below: