DateTimeOffset: TZ Offset is reset to +00:00 when fetching data from LiteDb collection

Question

When inserting, the offset is OK, however when retrieving the document, it's resetting to +00:00

Property:

public DateTimeOffset CreatedOn { get; set; }

Insert:

user.CreatedOn = DateTimeOffset.Now; // 01/20/2021 6:05:21 PM +05:30
col.Insert(user);
col.EnsureIndex(x => x.Username);

Find:

using (var db = _liteDbConnection.Create() as LiteDatabase)
{
   var col = db.GetCollection<AuthUser>(USERS_COLLECTION);
   return col.FindOne(x => x.UserId == userId);
}

user.CreatedOn becomes

01/20/2021 6:05:21 PM +00:00

Am I doing something wrong?

Answer 1

From the documentation

Following the BSON specification, DateTime values are stored only up to the miliseconds. All DateTime values are converted to UTC on storage and converted back to local time on retrieval.

It doesn't look like there's actual DateTimeOffset support. (Personally I think it's a terrible idea to convert to local time on retrieval, but that's a slightly different matter.) Additionally, it looks like it's not really converting to local time properly, given the incorrect offset of 0.

I would suggest avoiding using DateTimeOffset with LiteDb until it's really supported (maintaining the offset).

Answer 2

This happens because LiteDB stores a DateTimeOffset using value.UtcDateTime. This is unfortunate, but it can't be changed due to compatibility. However, you can override this behavior by creating a custom serializer:

BsonMapper.Global.RegisterType<DateTimeOffset>
(
    serialize: obj =>
    {
        var doc = new BsonDocument();
        doc["DateTime"] = obj.DateTime.Ticks;
        doc["Offset"] = obj.Offset.Ticks;
        return doc;
    },
    deserialize: doc => new DateTimeOffset(doc["DateTime"].AsInt64, new TimeSpan(doc["Offset"].AsInt64))
);