Description
The release notes for Npgsql 6 list breaking changes.
Npgsql 6.0 brings some major breaking changes and is not a simple in-place upgrade. Carefully read the breaking change notes below and upgrade with care.
The design change in handling DateTime and DateTimeOffset requires little changes in JADNC itself, but it mostly affects API clients. And because of that, our suite of integration tests.
As explained in https://www.roji.org/postgresql-dotnet-timestamp-mapping, the PostgreSQL column types "timestamp without time zone" (timestamp) and "timestamp with time zone" (timestamptz) both do NOT store time zone information and both do NOT store a UTC offset. They store solely a timestamp, which is always in UTC. This means that a DateTimeOffset with a non-zero UTC offset cannot be stored in these columns.
The old provider would auto-convert offsets to UTC, as well as convert between local dates and UTC dates (based on the time zone that the code is executing in), which led to a lot of confusion. The new provider fails when using any DateTime/DateTimeOffset that is not explicitly in UTC when mapped to column type timestamptz (the default). Alternatively, when the column type is timestamp, it only accepts local values.
As explained in the blog post, using UTC offsets without a time zone is a flawed design, resulting in subtle bugs. For details, read https://blog.nodatime.org/2011/08/what-wrong-with-datetime-anyway.html. And given that these columns cannot store an offset, it makes sense to optimize for the "UTC everywhere" pattern.
For JSON:API clients, this means they must now send a local time (example: "2001-02-03T04:05:06") for columns of type timestamp. And a time in UTC with offset 0 (examples: "2001-02-03T04:05:06Z", "2001-02-03T04:05:06+0:00") for columns of type timestamptz. Anything else will be rejected. This puts more burden on clients, who now have to account for these restrictions. It's tempting to adapt JADNC to do the conversion that the provider used to do. But I think the new behavior makes more sense for JADNC too. It greatly simplifies the behavior of JSON:API servers: dates are either in "the" local time zone (whatever that may be), or dates are UTC timestamps. The API developer chooses per column between these, and the client must adapt to that. Ultimately the client is in the best position to do conversions, if needed, based on the time zone where it resides.
Since the JSON:API server does not know in which time zone a client lives and the offset cannot be stored, it cannot roundtrip an incoming date with offset +5 in the response to the client. Also, the time zone of the JSON:API server and the time zone of the database server should not influence how the API handles time. The old behavior would get it wrong half of the year when DST is in effect.