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).

> Adding a month with + INTERVAL '1 months' is timezone-dependent. […]

Adding months isn’t well-defined anyway, even when using date, for days of month > 28. I think it’s a mistake that systems generically allow such a computation (as opposed to application code implementing domain-specific business rules).

I think most of the confusuion comes from "AT TIME ZONE" being the syntax for both the conversion from _and_ to timezone'd timestamps. Maybe it would have been more intuitive had the two operations gotten different wordings, e.g.:

timestamp to timestamptz: AS ZONED AT TIME ZONE ...

timestamptz to timestamp: AS LOCAL AT TIME ZONE ...

Makes me wonder what kind of footgun we are talking about here — is it classic, smoking, standard or vanilla?

Postgres 'at time zone' is confusing, but it's internally consistent. Once you understand what it's doing, you can work with it and it will reliably behave the way it's designed to.

https://oneuptime.com/blog/post/2026-01-25-postgresql-timezo...

    -- TIMESTAMPTZ AT TIME ZONE 'X' -> returns TIMESTAMP in timezone X
    -- TIMESTAMP AT TIME ZONE 'X' -> returns TIMESTAMPTZ treating input as timezone X

good lord, now we have LLM-speak "footguns" in article titles. what have we come to.

what about the load bearing real unlocks of the footguns?!??!?

https://i.programmerhumor.io/2026/09/09db9203bc618d8704855e1...