DateTime in filter expression gets parsed as local DateTime when using "Z" designator

[max-maurice]

DateTime in filter expression gets parsed as local DateTime when using "Z" designator

[max-maurice]

Thanks Bart, you're right! It's not JsonApiDotNetCore, but my implementation of a ValueConverter. I had

public class LocalDateTimeConverter : ValueConverter<DateTime, DateTime>
{
  public LocalDateTimeConverter()
      : base(v => v /* <- WRONG! */, v => DateTime.SpecifyKind(v, DateTimeKind.Local))
  {
  }
}

where the first ctor parameter was missing the conversion .ToLocalTime() and should read v => v.ToLocalTime().

Sorry for the troubles!

[bkoelman]

From what I can tell, JsonApiDotNetCore parses the "Z" designator correctly:

It's then up to the database driver to convert that to SQL. And that's where it gets tricky.

For example, Npgsql took a breaking change in 2020 and decided to fail on any DateTime or DateTimeOffset that isn't UTC because of all the non-intuitive behavior during conversions. We have several integration tests (1, 2, 3, 4) to verify that a local or UTC DateTime roundtrips properly, with PostgreSQL at least.

For SQL Server, there's a workaround described here, that deals with the fact that the underlying column type is unable to store whether the value is local or UTC. The consensus from the EF Core team is described at dotnet/efcore#4711 (comment), which comes down to: when using SQL Server, use DateTimeOffset instead of DateTime.

Would using DateTimeOffset (with the appropriate DATETIMEOFFSET underlying column type) work for you?

[bkoelman]

In case you're interested, there's a fun video at https://www.youtube.com/watch?v=ZLJLfImuFqM&list=PLdo4fOcmZ0oX-DBuRG4u58ZTAJgBAeQ-t discussing dates, offsets, and time zones. It has some interesting details, for example: did you know that DateTimeKind actually has four values internally?