Postgres `AT TIME ZONE 'UTC'` does NOT do what you think it does
Footguns with Postgres "at time zone 'UTC'"
Postgres's `AT TIME ZONE 'UTC'` converts `timestamptz` to `timestamp`, which lacks timezone info and breaks equality comparisons. Adding months is timezone-dependent, so you must convert to UTC first, but the result is a `timestamp`. To fix equality, you need to apply `AT TIME ZONE 'UTC'` twice, as in `(<timestamptz_column> AT TIME ZONE 'UTC' + INTERVAL '1 months') AT TIME ZONE 'UTC'`.
Comparing a `timestamp` and `timestamptz` will always result in `false`.
- heurekamala
The text says: "Comparing a timestamp and timestamptz will always result in false."
That is wrong. Postgres changes the timestamp to timestamptz very quietly in the background.
Wether it uses the time zone of the session for this.
If it is true or false depends on the TimeZone setting. This is more bad than "always false".
In production with UTC it works. On a laptop of a California developer it does not work.
Just tested:
SET TIME ZONE 'America/Los_Angeles';
SELECT '2026-03-01 00:00:00'::timestamp = '2026-03-01 00:00:00+00'::timestamptz; /* f */
SET TIME ZONE 'UTC';
SELECT '2026-03-01 00:00:00'::timestamp = '2026-03-01 00:00:00+00'::timestamptz; /* t */
If you can, use Postgres 16.
The double AT TIME ZONE 'UTC' thing from the text is not needed there. There you have date_add with a time zone as the third argument:
date_add(b.month_start, interval '1 month', 'UTC')
This adds the month in UTC and it stays a timestamptz.
- ulrikrasmussen
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).
- Macha
My experience is (despite the Postgres developer's advice to use it), timestamptz is largely a useless data type since it is just a wrapper around conversion to UTC.
- Are you storing past events? Just store them as UTC. Maybe you use timestamptz to do it for you, but it's actually a more obtuse interface for that than timesstamp
- Are you storing future UTC times? Great, just use UTC, see above
- Are you storing future human times? Then timestamptz is actively harmful because it eagerly converts to UTC so even if you get an updated tzdb in time for when the event comes due, you don't know what happened at write time so now your datetime is ambiguous. It's less broken to use a plain timestamp + string timezone column (if you need to sort by it, maybe a denormalized _utc column too, with the understanding that you'll need to regenerate it or accept slight off-by-one errors when you update the tzdb, but at least you can do this when you know what the input value was, unlike with timestamptz)
- 1a527dd5
- layer8
> 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).