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.
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).
A ZonedDateTime is not an instant and a timezone, for the same reason that you can’t unambiguously round trip between arbitrary timezones and UTC:
- 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)
Rookie pseudo-footgun - assumed skill exceeds capability error - usually accompanied with blameshifting pronunciation as demonstrated here. Anyone familiar with UTC/tz from another platform (e.g. excel) would be mostly immune or able to quickly identify and remedy.
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).
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...
postgres is sane and consistent. Some things could seem weird, but those usually track to SQL standards, which are weird and dated, but at least consistency is still there. On the other hand, I never can be sure where mysql/mariadb decided to improve my life.
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.
I suspect they meant more generally “the date&time field”, but a store in Boston that closes at 7:30 PM Eastern time (ET) closes on different UTC dates when ET is EST vs when ET is EDT.
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.
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:
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.
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...
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).
A ZonedDateTime is not an instant and a timezone, for the same reason that you can’t unambiguously round trip between arbitrary timezones and UTC:
- 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)
Ah, yes, you are right! Forgot about those two details
SET TIME ZONE 'UTC'; // or GMT, PST, etc.
Makes me wonder what kind of footgun we are talking about here — is it classic, smoking, standard or vanilla?
Rookie pseudo-footgun - assumed skill exceeds capability error - usually accompanied with blameshifting pronunciation as demonstrated here. Anyone familiar with UTC/tz from another platform (e.g. excel) would be mostly immune or able to quickly identify and remedy.
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).
Git gud. Don’t mix derived data from recorded data. Record data with the information available, then normalise for processing.
Don’t be a monkey.
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...
postgres is sane and consistent. Some things could seem weird, but those usually track to SQL standards, which are weird and dated, but at least consistency is still there. On the other hand, I never can be sure where mysql/mariadb decided to improve my life.
Oh believe me, I dislike mariadb for other reasons, like the fact that it sucks at picking indexes for queries.
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.
I've seen a product where they used UTC to store opening hours. They had to rewrite all dates via a script twice a year.
Why would the dates need rewriting?
I suspect they meant more generally “the date&time field”, but a store in Boston that closes at 7:30 PM Eastern time (ET) closes on different UTC dates when ET is EST vs when ET is EDT.
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.
So how are you solving the example in the article where they join on timestamps? Read both tables from the DB into the application?
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:
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.
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.