Litedb: DateTime strange UTC(?) offsetting between inserting and selecting

[Szymaniuk]

I have such data model:

public class MyObject
{
        [BsonId]
        public long Id { get; set; }

        [JsonConverter(typeof(Iso8601DateTimeConverter))]
        public DateTime? DueTo { get; set; }
}

This DateTimeConverter, is:

public class Iso8601DateTimeConverter : IsoDateTimeConverter
    {
        public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
        {
            var dateTime = (DateTime)value;
            serializer.Serialize(writer, dateTime.ToString("yyyy-MM-ddTHH:mm:ssZ"));
        }
    }

When data comes from API, it's like in below example:

{
    "Id": 98,
    "DueTo": "2014-01-01T06:00:00Z"
}

From code perspective, DueTo field is 2014-01-01 at 06:00 AM.

But when I'm inserting/upserting data into LiteDB, it's beeing converted into 2014-01-01 at 07:00 AM - data format is not the problem here, but the strange offset.

The question is why?
When I've looked into my_db_file.db, inside it's 2014-01-01 07:00:00.000 string.

What can I do, to ommit data inconsistency between inserting and selecting DateTime in LiteDB? Is there any special BSON attribute to be set?

[mbdavid]

Hi @Szymaniuk, normal JSON format doesn't support dates. So, when LiteDB parse this "date" as simple "string".

To fix this, LiteDB implement and extended JSON format. Special types use an embedded document like { $type: "string value" }. It´s same used in MongoDB.

In this case, your document must be:

{
    "Id": 98,
    "DueTo": { "$date": "2014-01-01T06:00:00Z" }
}

All other types can be see here:

https://github.com/mbdavid/LiteDB/wiki/Data-Structure#json-extended

[Szymaniuk]

Strange... I see there, that All DateTime values converts to UTC before store and converts back to local on retrieve.
Is there any option to override default behaviour implementation, to achieve ...and converts back to UTC on retrieve. ?

I found only ungly solution to convert DateTime value right just after selecting object from LiteDB - i.e.:
myObject.DueTo = TimeZone.CurrentTimeZone.ToUniversalTime(myObject.DueTo.Value);
but indeed it's really ugly, and I'm looking for something more generic, for all DateTime fields in all objects.

[Szymaniuk]

@mbdavid what do you think, to change:
https://github.com/mbdavid/LiteDB/blob/21bebf439125401d76490dd1e4e6bbad502b9473/LiteDB/Document/Bson/BsonReader.cs#L113
+
https://github.com/mbdavid/LiteDB/blob/21bebf439125401d76490dd1e4e6bbad502b9473/LiteDB/Document/Json/JsonReader.cs#L158
from:
.ToLocalTime()
to:
.ToUniversalTime()
?

For me, it's fixes all problems with DateTime. I don't know what was the intention to:
All DateTime values converts to UTC before store and converts back to local on retrieve.

From my PoV all DB things related with DateTime, should be done in UTC/non changed date time. All time zones changes should be stricted to UI/external API communication.

[mbdavid]

ok, I understand now. To change this must be add a parameter because it break api. For now, you can change how serialize/deserialize a bson data, like here:

https://github.com/mbdavid/LiteDB/issues/738#issuecomment-335248000

[StefanKoell]

Hi,

we're facing the same issue with DateTime. We tried to implement the solution based on #738 like this:
BsonMapper.Global.ResolveMember = (type, memberInfo, member) =>
{
if (member.DataType == typeof(DateTime))
{
member.Serialize = (o, m) => DateTime.Parse(o.ToString());
member.Deserialize = (v, m) => v.AsDateTime.ToString("o");
}
};

2 issues:

1. Setting a breakpoint within the action shows that the code is actually never executed.
2. I'm also not sure if the above conversion from and to DateTime to/from string resolves the issue and is the best solution in terms of performance.

Can you provide a sample on how to "fix/workaround" the UTC issue with 4.0?

Thanks,
Stefan

[mbdavid]

Hi @StefanKoell, it's on my list for 4.1: BsonSettings for works with DateTime in UTC or Local

[StefanKoell]

That's great news! Any workaround we can use for now?

[mbdavid]

Are you converting POCO class into BsonDocument? Or are you working direct with BsonDocument?

[StefanKoell]

I'm working with BsonDocuments directly.

[StefanKoell]

I see where you are going ;) I've changed the datetime to UTC after reading it from the BsonDocument. Now all is good. Once you introduce the BsonSettings for UTC, I will revert to the original code.

Thanks for the fast response!

[mbdavid]

Oh, thats why ResolveMember are now working, it's only to BsonMapper between POCO class to BsonDocument.
The only workaround for now is change source code here.

[Szymaniuk]

@mbdavid so from 4.1.0, there need to be used utc=true in connection string, in order to use this feature/fix, right? https://github.com/mbdavid/LiteDB/releases

Thanks.

[mbdavid]

Hi @Szymaniuk, yes. By default, BSON deserialization keeps DateTime as Kind=Local to be compatible. If you want work with UTC, you must define in connection string (or if you use LiteEngine you can specify in ctor).

[BennieCopeland]

Perhaps instead, store the Kind as well? I just got bitten by this myself. In my application the dates are all Kind=UTC. I was trying to compare Newtonsoft JSON deserialized event message data by value with what I was getting back from LiteDB, and they were not matching up like I expected.

[mbdavid]

@BennieCopeland, there is no option to store DateTimeKind in BSON format. Maybe in future I will made my own serialization format to implement better (like RavenDB do)