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

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

Ah, yes, you are right! Forgot about those two details

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.

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

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 according to the zone rules. Vice versa does not.

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.

People who handle this footgun properly tend to come from two groups:

- Those with safety training for this class of foot-pointed firearms, who know how dumb shit looks like and that they should not do it;

- Those with officer training who are able to recognize the higher-level categories, and take principled approach to safety - e.g. recognizing that "date", "timestamp, "duration", "time of day", "time of week", etc. are different concepts and should not be mixed.

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

Oh believe me, I dislike mariadb for other reasons, like the fact that it sucks at picking indexes for queries.

I suspect they meant more generally “the date&time field”, but a store in Boston that opens at 7:30 AM and closes at 7:30 PM Eastern time (ET) closes on different UTC date than it opens when ET is EST but opens and closes on the same date when ET is EDT, so it’s plausible that the dates actually needed to be updated.

I don't know the exact use case, but it sounds weird. If UTC is the single source of truth, then when you display the data and want to change some business assumptions, you only need to change the application logic. The underlying data remains unchanged.

One scenario I can imagine is when you want to store the results of business calculations in the database. In that case, once the algorithm changes, you may also need to update the stored data. This may be necessary for performance reasons.

In all other cases, calculating the result on the fly solves the problem and does not require a database update when the business rules change.

This is the only way. To do otherwise smacks of poor programming practice and is very often a sign the coder doesn't habitually consider the world outside their own timezone.

I read raw data (UTC timestamps) from the db and timezone for user profile and calculate it on the fly.

We can think of the split between DB and application in terms of DX or code, or we can think of it in terms of colocation of data and compute. The latter case will be compelling sometimes.

And of by one errors

Obviously there are more but the original joke circa 1999 was this. Made Y2K even more exciting.

timestamp doesn't contain the timezone information. Technically, it doesn't represent a time in the real world.

Unfortunately this is just as misleading.

It's correct that `timestamp` doesn't contain timezone information, but *neither does `timestamptz`. All `timestamptz` is is UNIX-style Epoch timestamp, that is an absolute point in time in UTC. It says nothing about the timezone because there are no time zones in that context.

The confusion arises because when you insert into a field you can insert "19:00 on the 29th March 2025 in London", but this is simply converted to UTC at insert time and the timezone is lost.

If anything, `timestamp` does represent a time in the "real world" and `timestamptz` represents a time in the universe.

If time zones are important to you, you need to record the time zone in a separate field. That is all. Where you store this depends on context, of course. You could store the user's current time zone in a profile and render all times in that time zone (psql and other clients do this automatically based on the OS time zone btw). Or you could store the time zone with each record if that makes sense, ie. you want to know what the wall clock time was for the user at the time the record was taken.

My bible: https://wiki.postgresql.org/wiki/Don't_Do_This

Presumably if the conversion logic changes. Eg user enters a future date in a place but the timezone in that place changes in the future.

The part that keeps biting teams is that AT TIME ZONE is a cast, not an annotation. On a timestamp it means "interpret these wall-clock digits as this zone and produce a timestamptz". On a timestamptz it means "render this instant as wall-clock digits in this zone and produce a timestamp". Same syntax, opposite direction, and the session TimeZone is the hidden third argument.

That is also why calendar math and instant math disagree. interval '1 day' on a timestamptz is 24 hours, so a 09:00 local appointment drifts across DST. The usual fix is to strip to timestamp in the civil zone, add the calendar interval, then cast back. The double AT TIME ZONE 'UTC' in the post is that pattern with UTC as the civil zone, which only works if the civil zone really is UTC.

What I have settled on: store events as timestamptz, force TimeZone=UTC on every connection (app, migrations, replicas, psql), and convert to a named zone only at the edge. timestamp without time zone is fine for things that are not instants (a store's opening hours, a birthday) and a footgun for anything that is.

TLDR Always use timestamptz (timestamp with time zone).

Only use timestamp (timestamp without time zone) if you really need local/plain datetime.

(Ideally, timestamptz would be called timestamp/instant, and timestamp would be called datetime.)

I love how there’s even errors in the article. Timestamps and time zones are so subtle and nuanced, and people assume they are WAY simpler than they really are. Even smart engineers.