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.