HN

Comments (58)

• Menu

  tibbar
  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-...
• Menu

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

  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).
• 1a527dd5
  My bible: https://wiki.postgresql.org/wiki/Don't_Do_This
• 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).
• Menu

  beybol
  I don't rely on time calculations at the database level. I handle them in the application based on UTC time stored in the database and the user's time zone. This shifts the problem to the application code, where I can control it precisely and make conscious decisions about how to handle specific business requirements, such as when a day ends or how to deal with events across different time zones. It also makes it possible to properly test all cases with unit tests.
• jokull
  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*".
• Felk
  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 ...
• 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)
• Menu

  nubinetwork
  The more I read about pgsql, the more I wonder why I would want to switch to it...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...
• epgui
  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.
• Menu

  braiamp
  This is why you store two dates: what the user sent and what you need for calculations. What the user sent becomes your oracle and what you show the user (date time + timezone) and your derived utc to do datetime arithmetic
• vivzkestrel
  - blog page not working?- https://bookofrevenue.com/blog/
• globular-toast
  > 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.
• ForHackernews
  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
• ScanMyTerms
  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.
• paulddraper
  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.)
• Menu

  walrus01
  [flagged]
• Menu

  cloudie78
  [flagged]
• Menu

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