4 ms·

You are thinking of UTC offsets as zones here, which is wrong. Yes, you can interpret an offset from the epoch in any utc offset and that's just a constant for

by Macha 2y ago

You are thinking of UTC offsets as zones here, which is wrong. Yes, you can interpret an offset from the epoch in any utc offset and that's just a constant formatting operation. But interpreting a zoned datetime as an offset against a point in UTC (or UTC+/-X) is not. You do not confidently know how far away 2025-03-01T00:00:00 America/New_York is from 1970-01-01T00:00:00+0000 until after that time. Even if you decide you're interpreting 1970-01-01T00:00:00+0000 as 1969-12-31T19:00-0500. Postgres assumes that 2025-03-01T00:00:00 America/New_York is the same as 2025-03-01T00:00:00-0500 and calculates the offset to that, but that transformation depends on mutable external state (NY state laws) that could change before that time passes. If you get news of that updated state before March, you now have no way of applying it, as you have thrown away the information of where that seconds since epoch value came from.

stickfigure 2y ago
I'm not quite sure what your point is. Postgres doesn't store time zones. "The internally stored value is always in UTC" from the documentation is false. It's not stored in UTC or any other zone. "it is always converted from UTC to the current timezone zone" is also false. It is not stored in UTC.
- Macha 2y ago
  This is pointless pedantry: Expressing it as the number of seconds since a point that is defined in UTC is a conversion to UTC by anyone else's logic (including, clearly, the documentation writers), even if there's not some bits in the database that say "this is UTC", even if that point can be expressed with various UTC offsets. The internal representation is just a integer, we all agree on that, this is not some great revelation. The fact that the internal representation is just an integer and the business rules surrounding it say that integer is the time since 1970-01-01T00:00:00Z is in fact the cause of the problem we are discussing here. The internal implementation prevents it being used as a timestamp with time zone, which its name and the ability to accept IATA TZs at the query level both in datetime literals and features like AT TIME ZONE or connection timezones strongly imply that it should be able to do. It also means the type is flawed if used to store future times and expecting to get back what you stored. We complain about behaviours like MySQL's previous silent truncation all the time, documented as they may have been, so "read the source code and you'll see it's doing XYZ" is not relevant to a discussion on if the interface it provides is good or bad. Nor is the link you shared the full story for the source code, as you'd need to look at the implementation for parsing of datetime literals, conversion to that integer value, the implementation of AT TIME ZONE, etc.
  - stickfigure 2y ago
    This is not pedantry. It has real-world consequences. Based on the misleading text in the documentation, the folks writing the Postgres JDBC driver made a decision to map TIMESTAMPTZ to OffsetDateTime instead of Instant. Which caused some annoying bugs in my 1.5M line codebase that processes financial transactions. Which is why I'm a bit pissed about all this. If you walk into a javascript job interview and answer "UTC" to the question "In what timezone is Date.now()?", you would get laughed at. I don't understand why Postgres people get a pass. If it's an instant, treat it as an instant. And it is an instant.
    - NoInkling 2y ago
      https://jdbc.postgresql.org/documentation/query/#using-java-8-date-and-time-classes https://jdbc.postgresql.org/documentation/query/#using-java-... > note that all OffsetDateTime instances will have be in UTC (have offset 0) Is this not effectively an Instant? Are you saying that the instant it represents can be straight up wrong? Or are you saying that because it uses OffsetDateTime, issues are being caused based on people assuming that it represents an input offset (when in reality any such information was lost at input time)? Also that page implies that they did it that way to align with the JDBC spec, rather than your assertions about misleading documentation.

by Macha 2y ago

stickfigure 2y ago

I'm not quite sure what your point is. Postgres doesn't store time zones. "The internally stored value is always in UTC" from the documentation is false. It's not stored in UTC or any other zone. "it is always converted from UTC to the current timezone zone" is also false. It is not stored in UTC.

Macha 2y ago
This is pointless pedantry: Expressing it as the number of seconds since a point that is defined in UTC is a conversion to UTC by anyone else's logic (including, clearly, the documentation writers), even if there's not some bits in the database that say "this is UTC", even if that point can be expressed with various UTC offsets. The internal representation is just a integer, we all agree on that, this is not some great revelation. The fact that the internal representation is just an integer and the business rules surrounding it say that integer is the time since 1970-01-01T00:00:00Z is in fact the cause of the problem we are discussing here. The internal implementation prevents it being used as a timestamp with time zone, which its name and the ability to accept IATA TZs at the query level both in datetime literals and features like AT TIME ZONE or connection timezones strongly imply that it should be able to do. It also means the type is flawed if used to store future times and expecting to get back what you stored. We complain about behaviours like MySQL's previous silent truncation all the time, documented as they may have been, so "read the source code and you'll see it's doing XYZ" is not relevant to a discussion on if the interface it provides is good or bad. Nor is the link you shared the full story for the source code, as you'd need to look at the implementation for parsing of datetime literals, conversion to that integer value, the implementation of AT TIME ZONE, etc.
- stickfigure 2y ago
  This is not pedantry. It has real-world consequences. Based on the misleading text in the documentation, the folks writing the Postgres JDBC driver made a decision to map TIMESTAMPTZ to OffsetDateTime instead of Instant. Which caused some annoying bugs in my 1.5M line codebase that processes financial transactions. Which is why I'm a bit pissed about all this. If you walk into a javascript job interview and answer "UTC" to the question "In what timezone is Date.now()?", you would get laughed at. I don't understand why Postgres people get a pass. If it's an instant, treat it as an instant. And it is an instant.
  - NoInkling 2y ago
    https://jdbc.postgresql.org/documentation/query/#using-java-8-date-and-time-classes https://jdbc.postgresql.org/documentation/query/#using-java-... > note that all OffsetDateTime instances will have be in UTC (have offset 0) Is this not effectively an Instant? Are you saying that the instant it represents can be straight up wrong? Or are you saying that because it uses OffsetDateTime, issues are being caused based on people assuming that it represents an input offset (when in reality any such information was lost at input time)? Also that page implies that they did it that way to align with the JDBC spec, rather than your assertions about misleading documentation.