How to deserialize DateTimeOffset during aggregation-CodePudding

I have the following code.

class TheThing
{
    public int Number { get; set; }
    public DateTimeOffset Date { get; set; }
}

static void Main(string[] args)
{
    var client = new MongoClient();
    var database = client.GetDatabase("test");
    var collection = database.GetCollection<TheThing>("theThings");

    var theThing = new TheThing()
    {
        Number = 1,
        Date = DateTimeOffset.UtcNow
    };

    collection.InsertOne(theThing);

    var theFirstItem = collection.Aggregate()
        .Group(new BsonDocument { { "_id", "Number" }, { "firstDate", new BsonDocument { { "$first", "$Date" } } } })
        .First();

    var firstDate = theFirstItem["firstDate"].ToUniversalTime();
}

The document saved in the database is the following.

{
    "_id" : ObjectId("62ba1b93fd0318e0c3db5935"),
    "Number" : 1,
    "Date" : [ 
        NumberLong(637919607231619015), 
        0
    ]
}

The document resulting from the aggregation pipeline is something like:

{
    "_id" : 1,
    "firstDate" : [ 
        NumberLong(637919607231619015), 
        0
    ]
}

Anyway, the last line throws an exception:

System.NotSupportedException: 'BsonArray does not support ToUniversalTime.'

How is it possible to get the DateTimeOffset back after an aggregation?

CodePudding user response：

The main problem is that MongoDB serializes the DateTimeOffset as a BsonArray of the ticks (long/Int64) and offset (in minutes). If you do not want to change this, you can deserialize it like this:

var array = theFirstItem["firstDate"].AsBsonArray;
var timestamp = array[0].AsInt64;
var offset = TimeSpan.FromMinutes(array[1].AsInt32);
var dto = new DateTimeOffset(timestamp, offset);

In MQL aggregations, you can use code similar to the following to calculate the time in UTC (unfortunately, .NET uses ticks since 0001-01-01 00:00 whereas MongoDB uses the Unix Epoch als reference point):

[{$set: {
  dt: {
    $toDate: {
      $subtract: [
        { $subtract: [
          { $divide: [ { $first: '$Date' }, 10000 ] }, // Convert ticks to ms
          62135596800000 ] // Convert to Unix epoch
        },
        {
          $multiply: [
            { $last: '$Date' }, 60000 ] // respect offset
        }]
      }
    }
}}]

If you can change the way how the data is stored and you do not need the offset part, you can adjust the serialization of the value like this:

[BsonRepresentation(BsonType.DateTime)]
public DateTimeOffset Date { get; set; }

Due to the use of the BsonRepresentation atribute, MongoDB stores the value as a UTC datetime without the offset. The benefit is that the ISODate in the database can be easily used in MongoDB queries. As all date values are stored in UTC they are easily comparable.

However, if you cannot do without the offset part, another option is to customize the deserialization of the BsonArray-DateTimeOffset, so that the conversion to DateTimeOffset is done during deserialization. See this link for details.