Footguns with Postgres “at time zone 'UTC'”
Discussion 89 comments
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 */
date_add(b.month_start, interval '1 month', 'UTC')
This adds the month in UTC and it stays a timestamptz.
Naming.
Timestamps.
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 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.
So in practice, while it was kinda useful to be locale/region dependant for some users it's probably been more trouble in the long run to be overly helpful.
C# was released after y2k, so they don't have the excuse.
Also, You're missing the biggest sin here however, locale specific time is OK, automatically allowing conversions/comparisons to points in time types without specifying timezones has in principle never caused anything but grief.
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).
- zoned datetimes carry ambiguities as to their actual location on the timeline (because they can repeat, or not exist at all)
- 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)
Sticky really only makes sense in two scenarios:
1. when all participants are assumed to be in the same geographic/political time zone for the foreseeable future (in which case the only advantage over point-in-time timestamps is future political changes to that region's time zone, like DST changes), or
2. when there's some privileged participant such that everyone else can assume events follow that participant's time zone (e.g. a company headquarters that moves very rarely, or an individual's personal wakeup alarms which can probably be assumed to follow their current location's time zone as they travel).
If you have a group of friends who like to stay in touch with regular group calls, and all/most of them are digital nomads who change their time zone of residence multiple times per year, you probably don't want the sticky paradigm.
I happen to call them "scientific datetime" vs "cultural/political datetime". However, the software dev industry has not converged on a standard vocabulary to delineate the 2 types which is unfortunate because that means programmers are unaware that the difference exists. Concepts are more top-of-mind when there are good names to label them.
If a programmer doesn't understand how the 2 datetimes behave differently, they will create software bugs as I've outlined before: https://news.ycombinator.com/item?id=39418897
There's the meme of "store UTC everywhere" (maybe perceived as correct because of superficial similarity to "use UTF-8 everywhere") ... but storing datetimes as UTC is only unambiguous for historical events such as timestamps of activity stored in server logs.
But future datetimes can have ambiguous edge cases which causes the split into 2 different types.
One way to manage is to store the datetimes with a timezone identifier and the offset, and when you load a new tzdb, go through and validate that the calculated offset matches the stored offset... for those events where they don't match, you have an exciting challenge of figuring out if the event should stay with the time zone or stay with the offset; both answers may be right ... ideally you inform the user(s) about what you've done and allow them to fix things software has messed up.
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.
> 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.
The thing about this is that if Perth's time zone ever changes so erratically or with such little notice that participants need to be notified that the point-in-time of an upcoming meeting has changed, it's no longer clear whether the participants would actually want the zoned "Perth at 10am" time to be canonical.
If athletes were flying in from around the world for an international competition tomorrow at 10am, and Australia decided to increase Perth's UTC offset by 1 hour as of today, would we expect all athletes, organizers, fans, etc. to show up and do everything 1 hour earlier (by solar time)?
"erratically" and "with little notice" are pretty relative when it comes to timezone shifts. DST decisions have been made with as little as a week lead time, Samoa dropping an entire day off of its calendar was done with under a year lead time (noises started about 9 months prior, the act was assented 6 months prior).
> would we expect all athletes, organizers, fans, etc. to show up and do everything 1 hour earlier (by solar time)?
Usually yes.
It has the zone offset and so is completely unambiguous and invariable.
private final LocalDateTime dateTime;
private final ZoneOffset offset;
private final ZoneId zone;
Postgres can only store 1) unzoned local time, or 2) UTC, with optional conversion on read/write.
The only purpose of the naive timestamp is to defer a necessary step of converting a sort of nominal prototype or template to a real moment on the timeline. Until you pin them down, they don't really represent moments.
It's like having a NULL in the timezone slot. I think it should mean "unknown, anything goes on a per-value basis!", rather than treating it like some polymorphic type that gets magically parameterized at runtime.
IMHO, it is a mistake of the standard and PostgreSQL authors to try to enable such sloppy thinking by users and applications. Ordering relations shouldn't even be implemented on naive timestamps. It should be a type error, not a trigger for implicit coercion.
Perhaps it should even be a domain over text or some composite type that represents the partially populated time info. Require explicit mutation to populate the missing bits and allow conversion to a well-defined moment.
I think a sane application should only use the timezone-aware timestamp for storage, and explicitly manage its own "timestamp templates" and conversions before trying to do comparisons on the timeline.
Edit to add: I think you can say the same about timeline versus some timestamp-with-timezone strings. Make it more explicit that the ordered type is normalized moments. Make sure there is a normalizable external representation like ISO timestamps.
Make it clear that other representations are not stable. E.g. any legal timezone that could have its definitions change over time is not a stable concept to use in a representation of a moment. It is also effectively naive unless it includes another version parameter to state which version of the legal definition is intended.
>AT TIME ZONE 'UTC' converts the data type from timestamptz to timestamp
surprising and a foot gun. Asking for at a timezone stripping the timezone makes no sense to me. Having `AT TIME ZONE 'UTC'` produce a timestamptz at +0:00 seems not insane.
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).
timestamp to timestamptz: AS ZONED AT TIME ZONE ...
timestamptz to timestamp: AS LOCAL AT TIME ZONE ...
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.
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*".
- 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 timestamp
- 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)
Granted, mariadb has weird time things too... but thats the tip of the iceberg between autoincrement with vacuum, vacuum in general, and that thread from the other day about bad migrations/version upgrades...
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.
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 XThat 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.
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.)
- 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.
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-...
UI time elements have to be presented in the user's TZ. In the DB one should store timestamptz in UTC for all things, and maybe also timestamptz in non-UTC TZs for user input (e.g., in a calendaring app).