Mongodb c# driver and ISODate

Question

I have the following test that passes:

namespace MongoDateTest
{

    [TestFixture]
    public class DateTesting
    {
        public class TestEntity
        {
            public string Id { get; set; }
            public string StringTest { get; set; }
            public DateTime DateTest { get; set; }

        }
        [Test]
        public void MongoDateConversion()
        {
            const string connectionString = "mongodb://localhost";
            var client = new MongoClient(connectionString);
            var server = client.GetServer();
            var database = server.GetDatabase("test");
            var collection = database.GetCollection<TestEntity>("entities");
            var entity = new TestEntity { 
                   Id = "1", 
                   StringTest = "Test",
                   DateTest = new DateTime(2013, 10, 13) //this is the date
            };
            collection.Save(entity);
            var id = entity.Id;
            var query = Query<TestEntity>.EQ(e => e.Id, id);
            var entityNew = collection.FindOne(query);
            Assert.AreEqual(entityNew.Id, entity.Id);
            Assert.AreEqual(entity.StringTest, entityNew.StringTest);

            //Assert.AreEqual(entity.DateTest,entityNew.DateTest);
            // This gives one day error:
            //  Expected: 2013-10-13 00:00:00.000
            //  But was:  2013-10-12 22:00:00.000
            //Assert.AreEqual(entity.DateTest.ToLocalTime(),entityNew.DateTest.ToLocalTime());
            // This gives a 2 hours error.
            //   Expected: 2013-10-13 02:00:00.000
            //   But was:  2013-10-13 00:00:00.000
            Assert.AreEqual(entity.DateTest, entityNew.DateTest.ToLocalTime());
        }
    }
 }

If I uncomment any of the Asserts.AreEqual I get an error (commented below).

The saved entity is:

{
"_id" : "1",
"StringTest" : "Test",
"DateTest" : ISODate("2013-10-12T22:00:00Z")
 }

I understand that this could be something related to ISODate and UTC (I am in UTC+1) but I a bit annoyed that my dates are saved with one day difference in the collection and requires me to convert to localTime any time I fetch some data with dates.

What is the reason for this behaviour and is there a way to avoid it?

Answer 1

In most case you want to store UTC date times in the database so your DateTime should be constructed as:-

DateTest = new DateTime(2013, 10, 13, 0, 0, 0, DateTimeKind.Utc) //this is the date

With this the first of your commented unit tests now passes.

Without specifying the DateTimeKind you are leaving it up to chance. MongoDB appears to assume that it's local and converts it to UTC in the database.

Note also that MongoDB DateTime values have less precision than .NET DateTime values. If you want to store arbitrary DateTime values and get them back in such a way that they still match then you will need to round them to the nearest millisecond before storing them.

If you really do want to store local times I recommend you switch from DateTime to DateTimeOffset and serialize it as a long Tick value for the UTC DateTime and a value for the offset.

Note that unless you store the offset calculated at the time the DateTime value was obtained then the .NET methods to convert to LocalTime are essentially useless since they do not know when daylight savings time started, nor do they even know what zone the DateTime value comes from. Overall, .NET DateTime handling leaves a lot to be desired and contains many misleading methods that claim to help but really do not.

Answer 2

You could also do this in your model. public class TestEntity

{
    public string Id { get; set; }

    public string StringTest { get; set; }

    [BsonDateTimeOptions(Kind = DateTimeKind.Utc)]
    public DateTime DateTest { get; set; }
}