MongoDB, C# and DateTime's

Storing DateTime in MongoDB using C# is easy. Just add a property of DateTime and off you go. Easy. Right? Well, MongoDB stores all date times as UTC, but you can tell the driver to deserialize back as e.g DateTimeKind.Local, but nevertheless, it will be stored in UTC (and maybe not returned with the resolution you think). Lets first have a look at a sample with DateTime and then have a look at DateTimeOffset to see if it solves the two issues: Local vs UTC and resolution. And finally, a custom serializer sample to force getting the local value of the DateTime in MongoDB.

DateTime Sample

Lets have a quick look at both Local and UTC DateTime with and without DateTimeKind. First lets define a document:

public class MyDoc
{
    [BsonId(IdGenerator = typeof(AscendingGuidGenerator))]
    [BsonRepresentation(BsonType.String)]
    public Guid Id { get; set; }
    
    public DateTime Local { get; set; }
    public DateTime Utc { get; set; }
    
    [BsonDateTimeOptions(Kind = DateTimeKind.Local)]
    public DateTime LocalWithKind { get; set; }
    
    [BsonDateTimeOptions(Kind = DateTimeKind.Utc)]
    public DateTime UtcWithKind { get; set; }
}

A simple helper for dumping out the date time instances:

static void Dump(string scenario, DateTime dt)
{
    Console.WriteLine(scenario);
    Console.WriteLine($"Kind: {dt.Kind}");
    Console.WriteLine(dt.ToString("O"));
    Console.WriteLine("------------------------------");
}

Setup a sample scenario:

var now = DateTime.Parse("2023-03-24T10:52:03.0930122+01:00");
var utc = now.ToUniversalTime();

Dump("Now", now);
Dump("Utc", utc);

Source date times:

Now
Kind: Local
2023-03-24T10:52:03.0930122+01:00
------------------------------
Utc
Kind: Utc
2023-03-24T09:52:03.0930122Z
------------------------------

and a document:

var myDoc = new MyDoc
{
    Local = now,
    LocalWithKind = now,
    
    Utc = utc,
    UtcWithKind = utc
};

await myDocs.InsertOneAsync(myDoc);

This will be stored as UTCs (as documented):

Local:          2023-03-24T09:52:03.093+00:00
LocalWithKind:  2023-03-24T09:52:03.093+00:00
Utc:            2023-03-24T09:52:03.093+00:00
UtcWithKind:    2023-03-24T09:52:03.093+00:00

RAW

{
  "Local": {
    "$date": {
      "$numberLong": "1679651523093"
    }
  },
  "LocalWithKind": {
    "$date": {
      "$numberLong": "1679651523093"
    }
  },
  "Utc": {
    "$date": {
      "$numberLong": "1679651523093"
    }
  },
  "UtcWithKind": {
    "$date": {
      "$numberLong": "1679651523093"
    }
  }
}

And returned as:

var returnedMyDoc = await myDocs
    .Find(d => d.Id == myDoc.Id)
    .SingleAsync();

Dump("Local", returnedMyDoc.Local);
Dump("LocalWithKind", returnedMyDoc.LocalWithKind);
Dump("Utc", returnedMyDoc.Utc);
Dump("UtcWithKind", returnedMyDoc.UtcWithKind);

Local ☹️
Kind: Utc 👈
2023-03-24T09:52:03.0930000Z
------------------------------
LocalWithKind 🤔
Kind: Local
2023-03-24T10:52:03.0930000+01:00
------------------------------
Utc 🤔
Kind: Utc
2023-03-24T09:52:03.0930000Z
------------------------------
UtcWithKind 🤔
Kind: Utc
2023-03-24T09:52:03.0930000Z
------------------------------

• ☹️ Local: Failed as it is returned as an UTC timestamp. And even though the value is correct (seen to UTC and apart from resolution), we expected a local timestamp.
• 🤔 LocalWithKind: Success, apart from resolution and with the issue that we don't really know what the offset was and current process local is used.
• 🤔 Utc & UtcWithKind: Success, apart from resolution

DateTimeOffset Sample

Minor change, lets switch out the DateTime for DateTimeOffset

public class MyDoc2
{
    [BsonId(IdGenerator = typeof(AscendingGuidGenerator))]
    [BsonRepresentation(BsonType.String)]
    public Guid Id { get; set; }

    public DateTimeOffset Local { get; set; }

    public DateTimeOffset Utc { get; set; }
}

var myDoc2 = new MyDoc2
{
    Local = now,
    Utc = utc,
};

await myDocs2.InsertOneAsync(myDoc2);

This time, MongoDB will store each property as an Array with information about when as well as the offset compared to UTC:

Local: Array
0:638152519230930122
1:60

Utc: Array
0:638152483230930122
1:0

RAW:

{
  "Local": [
    {
      "$numberLong": "638152519230930122"
    },
    60
  ],
  "Utc": [
    {
      "$numberLong": "638152483230930122"
    },
    0
  ]
}

Not very descriptive when looking at it and harder to work with natively. But it works. After reading the document back, we can se that it works as expected, both seen to Local vs UTC and resolution:

var returnedMyDoc2 = await myDocs2
    .Find(d => d.Id == myDoc2.Id)
    .SingleAsync();

Dump("Local-Offset-LocalDt", returnedMyDoc2.Local.LocalDateTime);
Dump("Local-Offset-UtcDt", returnedMyDoc2.Local.UtcDateTime);
Dump("Utc-Offset-LocalDt", returnedMyDoc2.Utc.LocalDateTime);
Dump("Utc-Offset-UtcDt", returnedMyDoc2.Utc.UtcDateTime);

Local-Offset-LocalDt
Kind: Local
2023-03-24T10:52:03.0930122+01:00
------------------------------
Local-Offset-UtcDt
Kind: Utc
2023-03-24T09:52:03.0930122Z
------------------------------
Utc-Offset-LocalDt
Kind: Local
2023-03-24T10:52:03.0930122+01:00
------------------------------
Utc-Offset-UtcDt
Kind: Utc
2023-03-24T09:52:03.0930122Z
------------------------------

• 🙂 Local-Offset-LocalDt: Local with offset and correct resolution.
• 🙂 Local-Offset-UtcDt: UTC with no offset
• 🙂 Utc-Offset-LocalDt: Local with offset and correct resolution.
• 🙂 Utc-Offset-UtcDt: UTC with no offset

Making it more semantic in MongoDB

I do think we can agree that this isn't very semantic when working with data directly in MongoDB. To solve this, we can make one minor change to the model, and that is to tell the driver to store the DateTimeOffset as a Document:

public class MyDoc2
{
    [BsonId(IdGenerator = typeof(AscendingGuidGenerator))]
    [BsonRepresentation(BsonType.String)]
    public Guid Id { get; set; }

    [BsonRepresentation(BsonType.Document)]
    public DateTimeOffset Local { get; set; }

    [BsonRepresentation(BsonType.Document)]
    public DateTimeOffset Utc { get; set; }
}

Still works as expected, but the change is within MongoDB:

Local: Object
    DateTime: 2023-03-24T09:52:03.093+00:00
    Ticks: 638152519230930122
    Offset: 60

Utc: Object
    DateTime: 2023-03-24T09:52:03.093+00:00
    Ticks: 638152483230930122
    Offset: 0

RAW:

{
  "Local": {
    "DateTime": {
      "$date": {
        "$numberLong": "1679651523093"
      }
    },
    "Ticks": {
      "$numberLong": "638152519230930122"
    },
    "Offset": 60
  },
  "Utc": {
    "DateTime": {
      "$date": {
        "$numberLong": "1679651523093"
      }
    },
    "Ticks": {
      "$numberLong": "638152483230930122"
    },
    "Offset": 0
  }
}

Custom Serializer

Well, the DateTime object is still stored as UTC in the DB and we know the offset, but what if we in MongoDB want to compare the DateTime against a local date time value? You could e.g. write your own serializer, looking something like this:

public class DateTimeOffsetSerializer : SerializerBase<DateTimeOffset>
{
    public static readonly DateTimeOffsetSerializer Instance = new();

    private static class Fields
    {
        public const string DateTime = "DateTime";
        public const string LocalDateTime = "LocalDateTime";
        public const string Ticks = "Ticks";
        public const string Offset = "Offset";
    }

    public override void Serialize(
        BsonSerializationContext context,
        BsonSerializationArgs args,
        DateTimeOffset value)
    {
        context.Writer.WriteStartDocument();

        context.Writer.WriteName(Fields.DateTime);
        context.Writer.WriteDateTime(
            BsonUtils.ToMillisecondsSinceEpoch(value.UtcDateTime));

        context.Writer.WriteName(Fields.LocalDateTime);
        context.Writer.WriteDateTime(
            BsonUtils.ToMillisecondsSinceEpoch(value.UtcDateTime.Add(value.Offset)));

        context.Writer.WriteName(Fields.Offset);
        context.Writer.WriteInt32(value.Offset.Hours * 60 + value.Offset.Minutes);

        context.Writer.WriteName(Fields.Ticks);
        context.Writer.WriteInt64(value.Ticks);

        context.Writer.WriteEndDocument();
    }

    public override DateTimeOffset Deserialize(
        BsonDeserializationContext context,
        BsonDeserializationArgs args)
    {
        context.Reader.ReadStartDocument();

        context.Reader.ReadName();
        context.Reader.SkipValue();

        context.Reader.ReadName();
        context.Reader.SkipValue();

        context.Reader.ReadName();
        var offset = context.Reader.ReadInt32();

        context.Reader.ReadName();
        var ticks = context.Reader.ReadInt64();

        context.Reader.ReadEndDocument();

        return new DateTimeOffset(ticks, TimeSpan.FromMinutes(offset));
    }
}

Then hook it in e.g. using an attribute on selective properties:

[BsonSerializer(typeof(DateTimeOffsetSerializer))]

Or for all DateTimeOffset properties:

BsonSerializer.RegisterSerializer(
	typeof(DateTimeOffset),
    DateTimeOffsetSerializer.Instance);

Using it results in the following in MongoDB:

Local: Object
    DateTime: 2023-03-24T09:52:03.093+00:00
    LocalDateTime: 2023-03-24T10:52:03.093+00:00
    Ticks: 638152519230930122
    Offset: 60

Utc: Object
    DateTime: 2023-03-24T09:52:03.093+00:00
    LocalDateTime: 2023-03-24T09:52:03.093+00:00
    Ticks: 638152483230930122
    Offset: 0

Summary

Using DateTimeOffset automatically gives you the correct resolution and combined with BsonRepresentation of BsonType.Document you also get a pure date entry to work with in MongoDB. If you for some reason don't want to know the exact offset and want to work with DateTime, you can make use of  BsonDateTimeOptions to get the full resolution for DateTime properties as well:

[BsonDateTimeOptions(
	Kind = DateTimeKind.Local,
	Representation = BsonType.Document)]

Even you want some custom representation, just write your own serializer.

Happy coding!

//Daniel