> There’s a variant of this advice which you’re more likely to hear from the PostgreSQL faithful: use TIMESTAMP WITH TIME ZONE or its convenient alias timestamptz. This stores the exact value in UTC and sounds like it might store the timezone too... but it doesn’t! All that’s stored is that UTC value, converted from whatever timezone was active or specified when the value was inserted.
This behavior was changed in a more recent edition of the SQL standard. Now, we have `TIMESTAMP WITH LOCAL TIME ZONE` which works the way `TIMESTAMP WITH TIME ZONE` used to work: basically just converting everything to/form UTC when it's stored/retrieved. The semantics for `TIMESTAMP WITH TIME ZONE` are now supposed to work more like the author's suggested approach: just gluing a time zone name to a regular old timestamp.
What are you talking about? Timestamp with local time zone is exactly the semantics that is broken and can corrupt your data by just having a server set-up wrong.
Postgres has had that exact semantics for ages, and the documentation explicitly tells you in very large text that it only exists for compatibility reasons and that you shouldn't use it.
The correct semantics for "timestamp with local time zone" is the naive one, that you can only recover using strings nowadays, thanks to the SQL standard braindead decision. But the good (?) news is that if you have a problem that requires a database, it's very likely that you will need to handle time zones anyway, so it's not a big issue.
I can't actually tell what you're saying. Postgres doesn't have a timestampltz. Only timestamptz. Oracle has both, but their timestampltz behaves like Postgres' timestamptz, and their timestamptz behaves the way the author is suggesting people should handle timestamps. Oracle is using the newer ISO semantics. I wish it were easier to get a copy of the ISO standard I used to access at work.
This behavior was changed in a more recent edition of the SQL standard. Now, we have `TIMESTAMP WITH LOCAL TIME ZONE` which works the way `TIMESTAMP WITH TIME ZONE` used to work: basically just converting everything to/form UTC when it's stored/retrieved. The semantics for `TIMESTAMP WITH TIME ZONE` are now supposed to work more like the author's suggested approach: just gluing a time zone name to a regular old timestamp.
I'm not sure when Postgres will adopt the newer type semantics. Oracle has for a while now: https://docs.oracle.com/en/database/oracle/oracle-database/1...