Storing Utc and Local datetime in Mongo

Question

I have a Mongo C# implementation that stores datetime as UTC.

MongoDB.Bson.Serialization.Options.DateTimeSerializationOptions options = 
    MongoDB.Bson.Serialization.Options.DateTimeSerializationOptions.UtcInstance;

var serializer = 
    new MongoDB.Bson.Serialization.Serializers.DateTimeSerializer(options);

MongoDB.Bson.Serialization.BsonSerializer.RegisterSerializer(
    typeof(DateTime),
    serializer);

I also have a need to store the user local timezone along with the UTC. To explain, I have two properties that goes like

DateTime WorkItemToCompleteBy{get; set;}
[BsonDateTimeOptions(Kind = DateTimeKind.Unspecified)]
DateTime WorkItemToCompleteByLocal{get; set;}

I'd like to store Australian/American/Indian/Other times in the Local property and the respective UTC value in the other one. Since am dealing with dozens of time zones, I have code that converts the UTC to the desired timezone and stores it in the WorkItemToCompleteByLocal property. I'd like Mongo to store this value 'as-is' and return it to me. The problem is that Mongo always stores it as ISODate and converts the value to Utc version. To explain. If UTC is 0730 Hours and I compute Brisbane Time to 1730Hours and set it to WorkitemToCompleteByLocal, they get saved as

"WorkItemToCompleteBy" : ISODate("2013-06-05T07:30:00Z"),
"WorkItemToCompleteByLocal" : ISODate("2013-06-05T12:00:00Z"),

Mongo interprets the time provided as local, the server being in India and coverts it to the equivalent UTC of 1200 hours. While it retrieves values back as 1730 (IST Albeit) It defeats my purpose and prevents me from running any local time based queries on Mongo. Am out of ideas. Any help is appreciated to help store the WorkItemToCompleteByLocal date 'As-Is' without modification

Gendron

I found a work around by fooling the system by recasting the 'Local' timezone as a UTC by hydrating a new DateTime(Local.Year,Local.Month......., Kind.Utc) and then using that value. Now, the data is stored as is and my logic knows Local column stores local value regardless of what the kind says (it says UTC because of the work around adopted). I will use this till I find a better answer. – Gendron

I've added an item to MongoDB's JIRA on dealing with local times, which also refers to this SO entry (needs a sign-up): jira.mongodb.org/browse/DOCS-4086 – Assuan

See https://mcmap.net/q/499548/-how-to-save-date-properly – Bacteria

Answer 1

New version of C# driver =>

public class MyMongoDBDateTimeSerializer : DateTimeSerializer {
    //  MongoDB returns datetime as DateTimeKind.Utc, which can't be used in our timezone conversion logic
    //  We overwrite it to be DateTimeKind.Unspecified
    public override DateTime Deserialize(BsonDeserializationContext context, BsonDeserializationArgs args)
    {
        var obj = base.Deserialize(context, args);
        return new DateTime(obj.Ticks, DateTimeKind.Unspecified);
    }

    //  MongoDB stores all DateTime as Utc, any DateTime value DateTimeKind is not DateTimeKind.Utc, will be converted to Utc first
    //  We overwrite it to be DateTimeKind.Utc, becasue we want to preserve the raw value
    public override void Serialize(BsonSerializationContext context, BsonSerializationArgs args, DateTime value)
    {
        var utcValue = new DateTime(value.Ticks, DateTimeKind.Utc);
        base.Serialize(context, args, utcValue);
    }
}

Complexioned

Answer 2

This is the way I force MongoDB to store the raw value, and ignore the DateTimeKind attribute in DateTime object.

This may not apply to your business logic, but make sense for us for our particular reasons.

BsonSerializer.RegisterSerializer(typeof(DateTime), new MyMongoDBDateTimeSerializer());


public class MyMongoDBDateTimeSerializer : DateTimeSerializer
{
    //  MongoDB returns datetime as DateTimeKind.Utc, which cann't be used in our timezone conversion logic
    //  We overwrite it to be DateTimeKind.Unspecified
    public override object Deserialize(MongoDB.Bson.IO.BsonReader bsonReader, System.Type nominalType, MongoDB.Bson.Serialization.IBsonSerializationOptions options)
    {
        var obj = base.Deserialize(bsonReader, nominalType, options);
        var dt = (DateTime) obj;
        return new DateTime(dt.Ticks, DateTimeKind.Unspecified);
    }

    //  MongoDB returns datetime as DateTimeKind.Utc, which cann't be used in our timezone conversion logic
    //  We overwrite it to be DateTimeKind.Unspecified
    public override object Deserialize(MongoDB.Bson.IO.BsonReader bsonReader, Type nominalType, Type actualType, MongoDB.Bson.Serialization.IBsonSerializationOptions options)
    {
        var obj = base.Deserialize(bsonReader, nominalType, actualType, options);
        var dt = (DateTime)obj;
        return new DateTime(dt.Ticks, DateTimeKind.Unspecified);
    }

    //  MongoDB stores all datetime as Utc, any datetime value DateTimeKind is not DateTimeKind.Utc, will be converted to Utc first
    //  We overwrite it to be DateTimeKind.Utc, becasue we want to preserve the raw value
    public override void Serialize(MongoDB.Bson.IO.BsonWriter bsonWriter, System.Type nominalType, object value, MongoDB.Bson.Serialization.IBsonSerializationOptions options)
    {
        var dt = (DateTime) value;
        var utcValue = new DateTime(dt.Ticks, DateTimeKind.Utc);
        base.Serialize(bsonWriter, nominalType, utcValue, options);
    }
}

Inferior

Doesn't seem to find MongoDB.Bson.Serialization.IBsonSerializationOptions in 2.0.1 – Blank

Great approach. Even if the C# driver would respect [BsonDateTimeOptions(Kind = DateTimeKind.Utc)], this solution will be better in most development use cases because it will apply globally and automatically to all DateTime properties, rather than having to specify each one and then having bugs when you inevitably forget one. – Collusive

Answer 3

In .net driver version 2.4, use this:

using MongoDB.Bson.Serialization;
using MongoDB.Bson.Serialization.Serializers;

BsonSerializer.RegisterSerializer(DateTimeSerializer.LocalInstance);

This way datetime values will be stored with kind=utc in db, but deserialized in local kind.

Stature

This is a much better approach. Here is the source code: github.com/mongodb/mongo-csharp-driver/blob/… – Evanston