MongoDB - search by date and time using C# driver
<p>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?</p>

<p>My POCO:</p>

<pre><code>public class TestClassForMongo
{
    public ObjectId Id { get; set; }
    public DateTime CreatedDateUtc { get; set; }
    public string Message { get; set; }
}
</code></pre>

<p>My search code:</p>

<pre><code>IMongoCollection&lt;TestClassForMongo&gt; collection = db.GetCollection&lt;TestClassForMongo&gt;("mongoTest");

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

<p>The above code returns empty array, although this:</p>

<pre><code>collection.Find(filterBuilder.Empty).First().CreatedDateUtc
</code></pre>

<p>Returns the date: "2016-03-04 21:21:54"</p>

<p>MongoDB 3.2.3, C# MongoDB driver 2.2.3</p>

<p>The driver docs: <a href="https://docs.mongodb.org/getting-started/csharp/query/" rel="nofollow">https://docs.mongodb.org/getting-started/csharp/query/</a></p>

<p><strong>THE ANSWER:</strong></p>

<p>I did not give enough information for anyone to answer this question, the problem was <strong>timezone and UTC related issues</strong>, quite basic ones too. I used <code>DateTime.UtcNow</code> to store the date in the database. It is stored as <code>"CreatedDateUtc" : ISODate("2016-03-04T21:21:54.836Z")</code>. Getting this in C# returns a date which is actually a UTC date (the <code>Kind</code> property is <code>UTC</code>), 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).</p>

<p>So one option would be to create the date for filter like this:</p>

<pre><code> new DateTime(2016, 03, 04, 21, 0, 0).ToUniversalTime()
</code></pre>

<p>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).</p>

<p>So actually the time stored was <code>22:21:54</code> 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.</p>