DateTimes lose their kind on persistence, becoming ambiguous

[DumpsterDoofus]

Description

According to the JSON:API recommendations, datetimes should use an unambiguous ISO 8601 format. However, it appears that DateTime.Kind is lost on persistence, causing subsequent operations to return timestamps with DateTimeKind.Unspecified, which is ambiguous:

For example, if you post a new entity with a datetime like 2020-11-04T01:30:53.113Z (which is unambiguous), the response datetime comes back like 2020-11-03T20:30:53.113 (which is ambiguous, especially if clients operate in a different timezone than the server).

Environment

• JsonApiDotNetCore Version: v4.0.0-alpha5

[bart-degreed]

Thanks for reporting this. I vaguely recall seeing something similar in the past.

There are basically two parts that play a role in this: Newtonsoft.JSON serialization settings and database date/time storage (which varies per provider and column type).

In the case of serialization, I think the best we can potentially do here (if it helps) is change our defaults in options.SerializerSettings, which are set during startup. Alternatively, we could hardcode something like was done in the past (this is the only occurrence in JADNC where we have data/time related logic).

In the case of database providers, picking a different column type may do the trick. Or perhaps switching to DateTimeOffset helps. If there are other settings I may not be aware of, please let me know.

To better understand what is going wrong in your case, we need some more info, like what database provider you are using, the column type etc. It would help if you can isolate the place where .Kind is lost. Does that happen during (de)serialization, fetching/updating in the database or both?

[DumpsterDoofus]

Dug further, and the loss of DateTimeKind happens when EF Core saves DateTimes. Although it got a bunch of downvotes, this comment sums up the situation well: use DateTimeOffset instead of DateTime if you care about unambiguous timestamps.

In retrospect, this makes sense, since Microsoft's official guidance is to use DateTimeOffset instead of DateTime, unless you have unusual needs (like storing ambiguous timestamps):

These uses for DateTimeOffset values are much more common than those for DateTime values. As a result, consider DateTimeOffset as the default date and time type for application development.

I tested using DateTimeOffset instead of DateTime with SQL Server as the database provider. It fixes the original bug (returned timestamps are ambiguous), and also has the advantage of preserving time zone info sent in the request (if it was sent).

@bart-degreed Feel free to close this issue, since a workaround exists and is probably a best practice anyways.

Workaround Test Notes

I tested on SHA 638a603 (latest master commit as of this comment) with SQL Server as the database provider. With these two resources:

public class DateTimeThing : Identifiable
{
    [Attr]
    public DateTime Timestamp { get; set; }
}

public class DateTimeOffsetThing : Identifiable
{
    [Attr]
    public DateTimeOffset Timestamp { get; set; }
}

In SQL Server, the DateTime was stored as datetime2(7), and the DateTimeOffset was stored as datetimeoffset(7).

DateTime test cases

• POST a UTC date: 2020-11-08T16:46:48.2416415Z
  • Returns ambiguous date, and it appears to be converted to the server timezone (in this case, -05:00): 2020-11-08T11:46:48.2416415
• POST same timestamp as before, but converted to a local date in a timezone with offset -8:00: 2020-11-08T08:46:48.2416415-08:00
  • Same as previous case (timestamp is converted to server timezone, but loses timezone info): 2020-11-08T11:46:48.2416415
• POST ambiguous timestamp: 2020-11-08T11:46:48.2416415
  • Comes back unchanged: 2020-11-08T11:46:48.2416415

DateTimeOffset test cases

• POST a UTC date: 2020-11-08T16:46:48.2416415Z
  • Returns unambiguous timestamp in UTC time zone: 2020-11-08T16:46:48.2416415+00:00
• POST same timestamp as before, but converted to a local date in a timezone with offset -8:00: 2020-11-08T08:46:48.2416415-08:00
  • Returns unambiguous timestamp in the timezone originally sent: 2020-11-08T08:46:48.2416415-08:00
• POST ambiguous timestamp: 2020-11-08T11:46:48.2416415
  • Returns unambiguous timestamp that assumes the ambiguous request timestamp was in the server timezone: 2020-11-08T11:46:48.2416415-05:00

[bart-degreed]

Thanks for investigating and sharing your results. I'm closing this issue, but its nice to have it for future reference.