The below is an attempt at a fresh look at Npgsql's date/time handling - especially around timestamptz; this has been a problematic point for a long while.

The current situation

• When reading timestamptz, we currently return a local DateTime, based on the timezone of the local machine where Npgsql is running (similarly, we reading a DateTimeOffset, we return one in the machine's local timezone).
• This behavior was implement to approximate the PostgreSQL text representation of timestamptz, which is a local timestamp based on the PostgreSQL TimeZone parameter.
  • Npgsql uses the local machine timezone and not the PostgreSQL TimeZone parameter because of the incompatibility between .NET's Windows timezone codes and the PostgreSQL IANA/Olson timezone codes. .NET 6 has added support for the latter, see Read DateTimeOffset with the PostgreSQL TIMEZONE #3594.
  • The NodaTime plugin does use the PostgreSQL TimeZone parameter, since NodaTime has always handled IANA/Olson timezones.

The problem

• The real meaning/use of timestamptz is to store a timestamp that is known to be in UTC (or rather, "a point in time that can be globally agreed-upon" regardless of timezone). This is in contrast to timestamp without time zone, which stores a date and time in an unknown/unspecified timezone. In that sense they correspond to NodaTime's Instant and LocalDateTime types, or to the BCL's DateTimeKind.Utc and DateTimeKind.Local. The PostgreSQL textual conversion to a local timestamp seems to be a somewhat unfortunate secondary effect.
  • Note that PostgreSQL now() function returns timestamptz, strengthening the idea that timestamptz represents a "point in time".
• We basically have no way for users to easily store UTC timestamps, and get them back as DateTime(UTC); this most closely matches the recommended UTC-everywhere pattern.
• You can't easily roundtrip timestamptz, since it gets converted based on your machine's timezone.
• Since we implicitly convert to/from UTC, it's easy for users to get confused and think values are round-trippable, which they are not; for example, we support writing DateTimeOffset with any offset, although the offset does not get saved in the database.
• It's currently really hard to work with timestamptz. When writing a DateTime(Utc) to a timestamptz column, we send a timestamp without time zone that gets converted in PostgreSQL, based on the connection TimeZone.
• We're inconsistent between read and write. For example, reading a timestamptz gives a DateTime(Local), but writing a DateTime(Local) sends a timestamp without time zone.
• The machine time zone where the .NET application is running is largely irrelevant in most modern applications: a single web server handles requests for users around the world, with many different time zones; the web server's timezone shouldn't matter.
• Similarly, a database connection state parameter such as TimeZone shouldn't be needed or play a significant role. TimeZone defaults to a PostgreSQL global configuration, which like the .NET machine timezone isn't relevant for most scenarios. The application can change this parameter, but again assuming international, multi-timezone usage, does not seem to be a productive way to program against the database.
• We've received multiple user complaints about the above, especially that receiving a machine-local DateTime is problematic; the introduction of an additional timezone conversion (from the PG UTC value to the local machine's) is frequently problematic and confusing.
• The timestamptz PostgreSQL binary representation (as opposed to the text one) is not a local timestamp, but the timestamp as-is (so UTC). A local timestamp may be appropriate when textually interfacing with a database, but not necessarily when writing applications against it.

Possible solutions

We could change Npgsql to no longer return local values for timestamptz:

• By default, we'd return DateTime(Utc) (with the exact value stored in PG, no conversions), instead of with Kind=Local
• If the user requests a DateTimeOffset, we'd return one with Offset=0, instead of with a local offset.
• When using NodaTime, timestamptz would be mapped to Instant, whereas timestamp without time zone would be mapped to LocalDateTime. Reading a timestamptz as ZonedDateTime, OffsetDateTime or DateTimeOffset would produce UTC only (offset=0), never local.

However, things get complicated when we look at writing:

• Currently, writing DateTime always sends timestamp without time zone (since DateTime(Kind=UTC) being converted to local when inserting to TIMESTAMP WITHOUT TIME ZONE #1803). It would be weird and inconsistent to read timestamptz as DateTime(Utc), but writing DateTime(Utc) as timestamp without time zone. We could revert this and go back to sending timestamptz for DateTime(UTC).
• We could revert to the previous behavior, where we send timestamptz for DateTime(Utc), and timestamp without time zone otherwise (revert DateTime(Kind=UTC) being converted to local when inserting to TIMESTAMP WITHOUT TIME ZONE #1803)
  • This would bring back DateTime(Kind=UTC) being converted to local when inserting to TIMESTAMP WITHOUT TIME ZONE #1803 was originally filed for: if we send timestamptz for DateTime(Utc) when inserting a DateTime(Utc), then if the user inserts that into a timestamp without time zone column, there would be a conversion.
  • I think the PG behavior here is questionable; applying a timezone conversion when converting from timestamptz to timestamp without time zone treats the latter as a local timestamp, where in fact it really is unspecified.
  • But in any case, this seems like the lesser of the two evils. If you want to store UTC timestamps, use timestamptz. Otherwise, modify the Kind on your DateTime before sending it.
• Implementation-wise this is annoying, since:
  • This violates our current type handler architecture - a type handler (and therefore PG type) is selected based on a CLR type, not based on the contents of the instance (the Kind).
  • We'd also need to infer for arrays. What do we do with heterogenous arrays of DateTime (some Utc, some Local)? We could check for these in validation and throw.
  • But implementation details shouldn't matter here, but rather desirable behavior.
• Another theoretical possibility is to map timestamptz to DateTimeOffset; but this doesn't correspond to how people usually want to work with UTC timestamps in .NET.
• NodaTime doesn't have any issues since the separation is done at the type level: Instant would be written as timestamptz, LocalDateTime as timestamp without time zone.

The breaking changes here are obviously pretty bad - we'd at least have a compatibility switch to go back to the current behavior.

Summary

• We ideally need one thing that clearly corresponds to timestamptz, and another to timestamp without time zone; this should be consistent across read and write.
• Unfortunately in .NET, these things are the same CLR type with different kinds: DateTime(Utc) and DateTime(Unspecified/Local). In NodaTime we have two clean types.
• In the past, we designed around the fact that timestamptz was outputted as a local timestamp in text mode, but we shouldn't let that be the primary thing.

Some history

• Review the extended Npgsql date types #347: Early thorough discussion in 2014 for 3.0. At this point we were reading timestamptz as UTC.
• NpgsqlDateTime local/UTC conversions don't take DST into account #690: For 3.0.1, change to read timestamptz as local DateTime. We still send DateTime with Kind=Utc as timestamptz, and other kinds as timestamp without time zone (@Emill).
• DateTime(Kind=UTC) being converted to local when inserting to TIMESTAMP WITHOUT TIME ZONE #1803: In 4.0, changed to always send DateTime as timestamp without timezone, regardless of the Kind..

Related issues

Selected issues where users request we switch to reading UTC, or encounter trouble because of our conversion to local:

• Ability to configure whether DateTimeOffset is deserialized as UTC (without offset) #2985
• DateTime paramter with UTC value is converted to Local datetime #2881
• UTC DateTime #2669
• UTC DateTime is not saved correctly when Postgres server is set to time zone other than UTC #2209
• UTC date is not sent as-is #1955
• @Emill: Read DateTimeOffset with the PostgreSQL TIMEZONE #3594
• @NinoFloris, @vonzshik

Other PG drivers

• JDBC maps timestamptz to OffsetDateTime (similar to .NET DateTimeOffset) with offset 0. https://jdbc.postgresql.org/documentation/head/java8-date-time.html.
• Python psycopg2: returns timestamptz as a local datetime (similar to DateTimeOffset), so corresponds more or less to the current Npgsql behavior. https://www.psycopg.org/docs/usage.html#time-zones-handling.

/cc @vonzshik @NinoFloris @Emill @Brar