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

In reply to

future zoned datetime carry outright uncertainty as to their actual location on the timeline (as the zone’s offset can be updated at any point and any number of times until the event has elapsed)

I wondered if it would be worth applying a (optional?) version to a timezone when stored, so you could distinguish between "whenever it is this time in Perth" vs "when I currently think this time will be in Perth, though if Perth changes its mind on how it offsets time, I want to keep what time I currently think that will be".

But you're right, that doesn't really add anything over storing it as UTC.

What’s the difference between these two other than how the client would convert to display to a user?

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 when zone rules change. The inverse 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

but the original joke circa 1999 was this.

No, the original joke, which GP is referring to, is “There are only two hard things in computer science. Naming things and cache invalidation.”

It predates 1999 and the Y2K bug by a fair amount. I first saw it on Usenet in the early 90s, around 1994 I think.

It made sense when databases and programs were used almost exclusively locally. It still makes sense for local apps (e.g. local-first or local-only smartphone and desktop apps) who typically will automatically do the right thing that way based on the OS regional settings.

It only started causing widespread issues with the rise of cross-region internet SaaS. Database systems, language runtimes, and OS APIs are keeping the default behavior for backwards compatibility.

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.

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)

I think poster overfits the postgres wiki advise. The thing is, most timestamps are not "UTC". That's only when you want to print an epoch value human readable you need some timezone and UTC is the most neutral. That doesn't mean you should opt for zoned timestamps in columns.

Because of this overfitting OP lands on exactly the wrong advice. Unfortunately the quoted line is "Even Postgres Wiki says: Don't use timestamp without time zone" but wiki says "Don't use timestamp (without time zone) *to store UTC times*".

This summer, with the help of AI, I found an inconsistency in the way Postgres handles timestamp vs. timestamptz comparisons under a DST spring-forward gap for the datetime_ops btree family [0]. Essentially there are scenarios where expression B > A and B < C, but also C = A, which can cause queries using a btree index (among other things) to return an incorrect result.

The assessment in the mailing list was that this was a bug, but there were no good ways to fix it.

Backpatching a behavioral change like this seems awfully scary. For the moment I'm just contemplating what we could potentially change in master. So far I don't like any of the choices :-(

[0] https://www.postgresql.org/message-id/flat/CA%2BCOZaDmCuOds-...

- blog page not working?

- https://bookofrevenue.com/blog/