Storing Utc and Local datetime in Mongo

问题:

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

回答1:

New version of C# driver =>

    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 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);
    }
}

回答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);
    }
}

回答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.