Comment by ulrikrasmussen

13 hours ago

The SQL standard is unfortunately really horrible when it comes to handling of time. The type `timestamp` is not a timestamp at all because it doesn't encode a unique point in time, it just stores a date and a time which has to be interpreted relative to a timezone. It should be called "datetime".

Moving a Java Instant back and forth between a database is also a surprisingly difficult task to do right, and it doesn't help that JDBC is just handling it completely wrong if you use its setTimestamp/getTimestamp methods. Not because it is a bad design with footguns, but because the implementation is just plain wrong and will corrupt your data if you deal with instants whose calendar date is far enough in the past due to it using the legacy date/time API which switches to the Gregorian calendar for dates in the past.

The name `timestamp with time zone` is also misleading because it doesn't actually store a time zone, it stores the number of seconds since epoch like a java.time.Instant (although at a different resolution). The "with time zone" part just refers to the textual format you denote the values in which includes the time zone after the date/time part to uniquely identify a timestamp, but the time zone is thrown away and not stored after the value has been parsed. This is different from e.g. `ZonedDateTime` in Java which will actually store the offset and therefore corresponds to a pair of (Instant, TimeZone).

30 comments

ulrikrasmussen

Reply
masklinn  13 hours ago

A ZonedDateTime is not an instant and a timezone, for the same reason that you can’t unambiguously round trip between arbitrary timezones and UTC:

- zoned datetimes carry ambiguities as to their actual location on the timeline (because they can repeat, or not exist at all)

- future zoned datetime carry outright uncertainty as to their actual location on the timeline (as the zone’s offset can be updated at any point and any number of times until the event has elapsed)

  • marcosdumay  7 hours ago

    Still, the GP is correct about the problems.

    Relational databases have exactly 1 type that corresponds to modern data-handling practices: timestamp with time zone, that stores a timestamp. There is no good way to store any other modern type, and the 1980s practices on time handling weren't actually very good.

    • masklinn  5 hours ago

      I'd say relational databases (in the sense of standard SQL) have 0 types that correspond to modern data-handling practices: timestamp with timezone stores an instant but lies about it and implicitly gets converted from and to the connection-local timezone.

  • tshaddox  6 hours ago

    It's always worth noting that future UTC timestamps are also ambiguous for certain operations, most notably computing durations, due to the unpredictability of leap seconds.

  • rawling  10 hours ago

    Has anyone proposed versioning timezones? Or is this such an edge case it would be overkill? (Either specify your future instant in UTC if you mean to stick to that, or specify it in a timezone and accept that it could change before it happens, or if you need something else get it in a contract and don't trust the computer!)

    • pseidemann  10 hours ago

      There is actually a simple heuristic you can use: if a point in time should be sticky to a calendar (e.g. calendar app or appointments which need to be synchronized between multiple humans or parties for a given context/location/region), store a datetime _without_ a timezone and make the timezone configurable for the user/infer it from the user. If you want a point in time which will not "physically" change, store a datetime _with_ a timezone, always, preferably UTC (e.g. logging, timers, measuring the occurrence of events).

      14 replies →

    • toast0  4 hours ago

      tzdb is versioned, but I can't recall if the individual timezones carry a version though...

      One way to manage is to store the datetimes with a timezone identifier and the offset, and when you load a new tzdb, go through and validate that the calculated offset matches the stored offset... for those events where they don't match, you have an exciting challenge of figuring out if the event should stay with the time zone or stay with the offset; both answers may be right ... ideally you inform the user(s) about what you've done and allow them to fix things software has messed up.

    • masklinn  10 hours ago

      It’s really not clear what you’re asking.

      The point of using zoned events is to match the life and expectation of people living in the real world e.g. if there is a meeting in Perth at 10AM, and the Perth timezone gets shifted, the meeting is still occurring at 10AM perth time. If it’s broadcast then every other time is what changes (or not).

      If people want to fix their meeting internationally they can already do that by setting their meeting time in UTC.

      4 replies →

  • paulddraper  10 hours ago

    A java.time.ZonedDateTime is not simply zoneid+date+time.

    It has the zone offset and so is completely unambiguous and invariable.

      private final LocalDateTime dateTime;
  private final ZoneOffset offset;
  private final ZoneId zone;

    Converting Instant+ZoneId into a ZonedDateTime can vary when zone rules change. The inverse does not.

officialchicken  13 hours ago

SET TIME ZONE 'UTC'; // or GMT, PST, etc.

  • crote  11 hours ago

    Except you shouldn't do the GMT/PST part, because it can lead to people falsely believing that Postgres can handle time zones, leading to silent data corruption when the time zone definition changes - such as due to abolishing DST.

    Postgres can only store 1) unzoned local time, or 2) UTC, with optional conversion on read/write.