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?

Does LiteDb support DateTimeOffset at all? From the [docs](https://www.litedb.org/docs/data-structure/) it looks like it only supports UTC `DateTime` even though it's stored using an offset — Panagiotis Kanavos, Mar 02 '21 at 11:37
[LiteDB doesn't support DateTimeOffset](https://github.com/mbdavid/LiteDB/issues/1686) and from the comments, it doesn't look like the maintainers want to add support any time soon — Panagiotis Kanavos, Mar 02 '21 at 11:40

Jon Skeet · Accepted Answer · 2021-03-02T11:42:37.627

6

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).

edited Mar 02 '21 at 11:42

answered Mar 02 '21 at 11:37

Jon Skeet

1,421,763
867
9,128
9,194

1

Perhaps you can convert store the time as a timestamp which is a long. So store DateTimeOffset.ToUnixTimeSeconds() when you store the data and DateTimeOffset.FromUnixTimeSeconds() when you retrieve it. – GlennSills Mar 02 '21 at 14:13
@GlennSills: That would certainly avoid the misleading information in the DateTimeOffset, yes. If the OP actually needs date/time and offset information, it looks like they'll need to store the offset in a separate database field. – Jon Skeet Mar 02 '21 at 15:39

score 2 · Answer 2 · answered Mar 05 '21 at 18:17

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

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

2 Answers2