Litedb: [BUG] LiteDB loses precision in DateTime

[dropsonic]

Version
LiteDB 5.0.8
.NET Framework 4.8 / .NET Standard 2.0

Describe the bug
LiteDB loses precision when serializes DateTime.

Code to Reproduce

class Foo
{
    public DateTime Value { get;set; }
}
...
var db = new LiteDatabase(@"C:\Temp\database.db")) { UtcDate = true };
var col = db.GetCollection<Foo>();
var expected = new Foo() { Value = new DateTime(637310234309996491) };
col.Insert(expected);
var actual = col.FindAll().First();
Console.WriteLine(actual.Value == expected.Value); // false
Console.WriteLine(actual.Value.Ticks) // 637310234309990000 instead of 637310234309996491

Expected behavior
DateTime is the same after serialization/deserialization to the database

Screenshots/Stacktrace
If applicable, add screenshots/stacktrace

Additional context
A similar issue was reported a while ago: https://github.com/mbdavid/LiteDB/issues/420

[lbnascimento]

@dropsonic This is not a bug, but a limitation of the BSON specification, which LiteDB implements. While LiteDB stores dates as the UTC milliseconds since the Unix epoch, C# internally stores DateTime as the amount of 100-nanosecond ticks since the Unix epoch. This additional precision is lost when serializing to BSON.

Your best option is probably to create custom serializer/deserializer for your class that serializes DateTime as an Int64 containing the ticks.

[dropsonic]

@lbnascimento it is a highly unexpected and non-documented behavior.
As far as I see from the source code (which was mentioned in #420), the serializer should use ticks by default: https://github.com/mbdavid/LiteDB/blob/321bd820a1b276423298b7a2a87f4b9200f11eb0/LiteDB/Engine/Disk/Serializer/BufferWriter.cs#L265-L270

Adding BsonMapper.Global.RegisterType<DateTime>(value => value.Ticks, bson => new DateTime(bson)); doesn't help.

[lbnascimento]

@dropsonic It is documented behaviour, but I agree it should be more clear.

This Write(DateTime) method is not currently referenced anywhere in the code - I believe it might've been used in earlier versions. The code that is actually being used is this: https://github.com/mbdavid/LiteDB/blob/321bd820a1b276423298b7a2a87f4b9200f11eb0/LiteDB/Engine/Disk/Serializer/BufferWriter.cs#L434-L442

Using RegisterType for basic BSON types (like DateTime) won't work. Using it for your POCO class that contains a DateTime should work, however.

[dropsonic]

@lbnascimento thanks for the link, didn't notice it when reading the article for the first time.
The necessity of using RegisterType for every POCO in the system that contains DateTime is quite annoying, won't you agree?

[henon]

I agree with @dropsonic, this is a massive pitfall which everyone who stores and compares loaded DateTimes will encounter.

How about keeping the current format how it is for backwards compatibility for DateTime values saved until now but save all future values without precision loss and denote the newly saved values accordingly so that they are not interpreted as old DateTime values?

Also, I don't understand, why does LiteDB have to follow the BSON spec in every detail even if it is to the user's disadvantage? Couldn't it handle DateTimes better and just document that deviation from the BSON spec, to all user's advantage?

[lbnascimento]

@dropsonic @henon
We will not make breaking changes to the file format at the current time. If changes have to be made in the future for other reasons, this issue might be revisited.

Personally, I agree with @henon that it would've been better not to follow the BSON spec (at least for this specific situation) from the start, but changing it now would introduce significant compatibility issues.

I changed the order in which types are serialized, so that it is now possible to create custom serializers/deserializers for basic types like DateTime.

[dropsonic]

@lbnascimento thanks!

[dropsonic]

@lbnascimento is there any estimation when the next version of LiteDB will be released to be able to consume this change?

[dropsonic]

Thanks!

If anyone is curious, I used the following code to make it work on 5.0.9:

var mapper = new BsonMapper();
mapper.RegisterType<DateTime>(
    value => value.ToString("o", CultureInfo.InvariantCulture),
    bson => DateTime.ParseExact(bson, "o", CultureInfo.InvariantCulture, DateTimeStyles.RoundtripKind));
mapper.RegisterType<DateTimeOffset>(
    value => value.ToString("o", CultureInfo.InvariantCulture),
    bson => DateTimeOffset.ParseExact(bson, "o", CultureInfo.InvariantCulture, DateTimeStyles.RoundtripKind));

using (var db = new LiteDatabase("instance.db", mapper))
{
    ...
}