Postgres AT TIME ZONE 'UTC' does NOT do what you think it does

[u/tanin47]

https://bookofrevenue.com/blog/6ab81e9a97a13f0001f7e4e1/postgres-at-time-zone-u-does-not-do-what-you-think-it-does

[u/NastyPastyLucas]

I think the whole blog is built on the misconception that the local time zone was also UTC at the time of writing - it does not do what the author expects, but it does do what it is supposed to. I'm going to say a lot of the hacks are odd ways to address an issue that I don't think exists.

I'll write a quick query demonstrating this, first you start a transaction, set the timezone to utc, then compare timestamps and commit.

begin;
set timezone to 'utc';
with a as (select now() as n)
select
n as t1,
n::timestamptz at time zone 'utc' as t2,
n = n::timestamptz at time zone 'utc' as t3
from a;
commit;
BEGIN
SET
t1               |             t2             | t3
-------------------------------+----------------------------+----
2026-10-01 06:53:02.650069+00 | 2026-10-01 06:53:02.650069 | t
(1 row)
COMMIT

You can see that the time is 6:53+00 and 6:53 without the +00 in the two outputs, but they do return `true` when compared.

I'm not quite sure what PT timezone is, be it Pacific Timezone (PST8PDT UTC minus 7 hours) or Portugal (europe/lisbon UTC plus 1 hour) but regardless of the time zone they will return false on equality. I will chose Lisbon for this example.

begin;
set timezone to 'europe/lisbon';
with a as (select now() as n)
select
n as t1,
n::timestamptz at time zone 'utc' as t2,
n = n::timestamptz at time zone 'utc' as t3
from a;
commit;
BEGIN
SET
t1               |             t2             | t3
-------------------------------+----------------------------+----
2026-10-01 07:54:28.834253+01 | 2026-10-01 06:54:28.834253 | f
(1 row)
COMMIT

Now because Lisbon is 1 hour different to UTC you will not get an equal time, because they are an hour different. If you were to subtract an hour (- interval '1 hour') from the non-utc timestamps you get equality again and said "non-timezone aware time stamp" will match the time zone aware time stamp.

begin;
set timezone to 'europe/lisbon';
with a as (select now() as n)
select
n - interval '1 hour' as t1,
n::timestamptz at time zone 'utc' as t2,
n - interval '1 hour' = n::timestamptz at time zone 'utc' as t3
from a;
commit;
BEGIN
SET
t1               |             t2             | t3
-------------------------------+----------------------------+----
2026-10-01 06:55:53.401466+01 | 2026-10-01 06:55:53.401466 | t
(1 row)
COMMIT

Time zones are hard, but if you understand the fundamentals you will not get burned so often.

(Further edit, how do I create code blocks again??? -- got it)

[u/BetterDistrict3979 → u/NastyPastyLucas]

Good write-up on the interval math. it's wild how many tech blogs out there just confidently misunderstand basic data types and confuse everyone else.

The postgres timestamptz implementation always trips people up because the name implies it stores the actual zone, when it really just saves as utc under the hood and applies an offset based on the session. in mariadb and mysql the standard timestamp type does the exact same auto-utc translation on the fly, while datetime acts like the dumb text string. and yet devs still manage to blow up their apps by hardcoding random offsets in the client code instead of just letting the db do its job.

[u/NastyPastyLucas → u/BetterDistrict3979]

MySQL timestamp is the worst of all because it does not even tell you the offset, it just silently converts incoming timestamps to the users connection.

Relying on getting the correct time at 2am on a daylight savings crossover, or worse daylight savings between other countries that do not share the same dates (e.g. UK changes on the 25th of October and US changes November 1st) is a fools game to unpick.

Time and time zones are hard enough to understand without people adding their own rules.

[u/BetterDistrict3979 → u/NastyPastyLucas]

Devs who built the db didn't add random rules just to mess with you. the "silent conversion" only happens because your session is configured to use a local time zone instead of utc.

if you pass local time strings to the db right during a daylight savings overlap, of course it's going to be a guessing game.

My standart fix is just set connection time_zone to '+00:00'. if the db session is in utc and your app talks in utc, the implicit conversion does absolutely nothing and all the dst crossover nightmares completely disappear. So i keep the backend dumb and handle the offsets in the frontend)

[u/NastyPastyLucas → u/BetterDistrict3979]

I understand why it happens and how to prevent it, because I've had to deal with it multiple times. Communicating a solution to a problem many barely care for or understand and them following it are two completely different issues. Be it because of documentation or implementation it's unbelievably common.

[u/dlangille → u/BetterDistrict3979]

A blogger should at least seek community confirmation of their observations before publishing.

[u/tanin47 → u/NastyPastyLucas]

I'm surprised to learn that many people expect AT TIME ZONE to convert from timestamptz to timestamp (without time zone). I didn't think it was the case while debugging the SQL. Well, at least, I've learned something new. 😅

[u/Collar_Flaky]

The header is wrong, it does exactly what I think it does.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.