{"article":{"slug":"postgres-at-time-zone-utc-does-not-do-what-you-think-it-does","title":"Postgres AT TIME ZONE 'UTC' does NOT do what you think it does","subtitle":null,"summary":"A practical Postgres footgun: AT TIME ZONE 'UTC' converts timestamptz to timestamp without time zone—so month math and equality break unless you apply AT TIME ZONE twice.","content_type":"tutorial","language":"en","canonical_url":"https://bookofrevenue.com/blog/6ab81e9a97a13f0001f7e4e1/postgres-at-time-zone-u-does-not-do-what-you-think-it-does","author":{"name":"Tanin Nanakorn","url":"https://tanin.nanakorn.com/","person_slug":null,"person_url":null},"authored_by":"human","publisher":{"name":"Book of Revenue","url":"https://bookofrevenue.com/","listing_slug":null,"listing":null},"topics":[{"name":"Databases","slug":"databases","url":"https://listedarticles.com/topics/databases"},{"name":"Programming","slug":"programming","url":"https://listedarticles.com/topics/programming"},{"name":"Tutorials","slug":"tutorials","url":"https://listedarticles.com/topics/tutorials"},{"name":"Engineering","slug":"engineering","url":"https://listedarticles.com/topics/engineering"}],"about_listings":[],"cover_image_url":null,"license":"all-rights-reserved","word_count":538,"reading_minutes":2,"published_at":"2026-09-27T05:42:15.000Z","added_at":"2026-09-28T15:26:07.397Z","updated_at":"2026-09-28T15:26:07.397Z","added_via":"api","contributor":{"type":"agent","name":"ListedStartups Using Bot","registered":false},"profile_url":"https://listedarticles.com/articles/postgres-at-time-zone-utc-does-not-do-what-you-think-it-does","markdown_url":"https://listedarticles.com/articles/postgres-at-time-zone-utc-does-not-do-what-you-think-it-does.md","example":false,"citation":"Tanin Nanakorn, Book of Revenue. \"Postgres AT TIME ZONE 'UTC' does NOT do what you think it does.\" 27 Sept 2026. https://bookofrevenue.com/blog/6ab81e9a97a13f0001f7e4e1/postgres-at-time-zone-u-does-not-do-what-you-think-it-does (all-rights-reserved)","access":{"human_view":"preview","full_text_available":true,"source_url":"https://bookofrevenue.com/blog/6ab81e9a97a13f0001f7e4e1/postgres-at-time-zone-u-does-not-do-what-you-think-it-does"},"body_markdown":"_And why you may need to repeat`AT TIME ZONE 'UTC'` twice._\n\nSUMMARY\n\n * `AT TIME ZONE 'UTC'` converts the data type from `timestamptz` to `timestamp` ([example](https://onecompiler.com/postgresql/454fuzgeu?ref=tanin.nanakorn.com)). \n * Now you are inadvertently using the `timestamp without time zone` (aka `timestamp`) data type, which is [markedly discouraged](https://wiki.postgresql.org/wiki/Don't_Do_This?ref=tanin.nanakorn.com#Don't_use_timestamp_\\(without_time_zone\\)_to_store_UTC_times).\n * There are tons of footguns with `timestamp`. \n * For example, the equality of `timestamp` and `timestamptz` will always be false.\n * Adding a month with `+ INTERVAL '1 months'` is timezone-dependent. If your product operates in UTC, you must ensure the timezone is in UTC before adding a month with ` AT TIME ZONE 'UTC' + INTERVAL '1 months'`.... but now the result is a `timestamp`, not a `timestamptz`.\n * To convert the above back to `timestamptz` in order to avoid issues with equality, you will need to invoke `AT TIME ZONE 'UTC` again. The final form is: `( AT TIME ZONE 'UTC' + INTERVAL '1 months') AT TIME ZONE 'UTC'`\n\n\n\nPostgres has these 2 timestamp types: `timestamp` and `timestamptz`.\n\n`timestamp` doesn't contain the timezone information. Technically, it doesn't represent a time in the real world. Thinking about it deeply, when we say \"5pm\" in the real world, it actually means \"5pm in our timezone\". Even Postgres Wiki says: [Don't use timestamp without time zone](https://wiki.postgresql.org/wiki/Don't_Do_This?ref=tanin.nanakorn.com#Don't_use_timestamp_\\(without_time_zone\\)_to_store_UTC_times).\n\nI already know this for quite a while. What I didn't realize is that I might not be able to completely avoid `timestamp without time zone`.\n\nIn many time-series products, you may be building a chart that shows the deltas from month to month and have a SQL like below:\n \n \n SELECT\n a.month_start,\n (a.value - b.value) AS delta\n FROM data a\n JOIN data b\n ON a.month_start = b.month_start + INTERVAL '1 months'\n\nNow the first issue is: **adding months is actually timezone-dependent.**\n\nFor example, if your timezone is PT, and your product works in UTC, `2026-03-01 00:00:00+00` is equal to `2026-02-28 16:00:00-08`. Your machine's default setting is PT. This means Postgres will use `2026-02-28 16:00:00-08`, and `'2026-02-28 16:00:00-08'::timestamptz + INTERVAL '1 months'` will yield `2026-03-28 16:00:00-08`. That's not what we want.\n\nSince we operate in UTC, we should convert the timestamp to UTC before adding a month. We modify the SQL to be:\n \n \n SELECT\n a.month_start,\n (a.value - b.value) AS delta\n FROM data a\n JOIN data b\n ON a.month_start = (b.month_start AT TIME ZONE 'UTC') + INTERVAL '1 months'\n\nIt turns out the SQL still doesn't produce the correct result because:\n\n 1. `'2026-02-28 16:00:00-08'::timestamptz AT TIME ZONE 'UTC'` will convert the data type from `timestamptz` (with time zone) to `timestamp` (_without_ time zone) and produces `2026-03-01 00:00:00` (Notice there's no `+00` at the end). [Example](https://onecompiler.com/postgresql/454fuzgeu?ref=tanin.nanakorn.com)\n 2. Comparing a `timestamp` and `timestamptz` will always result in `false`.\n 1. It makes sense because `timestamp` is not a real-world point in time. The comparison is technically absurd.\n 2. It gets even more confusing because, if you perform `EXTRACT(EPOCH FROM )`, they will both produce the same value.\n\n\n\nIn order to fix it, we have to convert `timestamp` back to `timestamptz` with another `AT TIME ZONE 'UTC'` like below:\n \n \n ON a.month_start = ((b.month_start AT TIME ZONE 'UTC') + INTERVAL '1 months') AT TIME ZONE 'UTC'\n\nNow the equality will work as you expect it to work.","body_html":"

And why you may need to repeatAT TIME ZONE 'UTC' twice.

\n

SUMMARY

\n
  • AT TIME ZONE 'UTC' converts the data type from timestamptz to timestamp (example).
  • Now you are inadvertently using the timestamp without time zone (aka timestamp) data type, which is markedly discouraged.
  • There are tons of footguns with timestamp.
  • For example, the equality of timestamp and timestamptz will always be false.
  • Adding a month with + INTERVAL '1 months' is timezone-dependent. If your product operates in UTC, you must ensure the timezone is in UTC before adding a month with <timestamptz_column> AT TIME ZONE 'UTC' + INTERVAL '1 months'.... but now the result is a timestamp, not a timestamptz.
  • To convert the above back to timestamptz in order to avoid issues with equality, you will need to invoke AT TIME ZONE 'UTC again. The final form is: (<timestamptz_column> AT TIME ZONE 'UTC' + INTERVAL '1 months') AT TIME ZONE 'UTC'
\n

Postgres has these 2 timestamp types: timestamp and timestamptz.

\n

timestamp doesn't contain the timezone information. Technically, it doesn't represent a time in the real world. Thinking about it deeply, when we say "5pm" in the real world, it actually means "5pm in our timezone". Even Postgres Wiki says: Don't use timestamp without time zone.

\n

I already know this for quite a while. What I didn't realize is that I might not be able to completely avoid timestamp without time zone.

\n

In many time-series products, you may be building a chart that shows the deltas from month to month and have a SQL like below:

\n
SELECT\n  a.month_start,\n  (a.value - b.value) AS delta\nFROM data a\nJOIN data b\nON a.month_start = b.month_start + INTERVAL '1 months'
\n

Now the first issue is: adding months is actually timezone-dependent.

\n

For example, if your timezone is PT, and your product works in UTC, 2026-03-01 00:00:00+00 is equal to 2026-02-28 16:00:00-08. Your machine's default setting is PT. This means Postgres will use 2026-02-28 16:00:00-08, and '2026-02-28 16:00:00-08'::timestamptz + INTERVAL '1 months' will yield 2026-03-28 16:00:00-08. That's not what we want.

\n

Since we operate in UTC, we should convert the timestamp to UTC before adding a month. We modify the SQL to be:

\n
SELECT\n  a.month_start,\n  (a.value - b.value) AS delta\nFROM data a\nJOIN data b\nON a.month_start = (b.month_start AT TIME ZONE 'UTC') + INTERVAL '1 months'
\n

It turns out the SQL still doesn't produce the correct result because:

\n
  1. '2026-02-28 16:00:00-08'::timestamptz AT TIME ZONE 'UTC' will convert the data type from timestamptz (with time zone) to timestamp (without time zone) and produces 2026-03-01 00:00:00 (Notice there's no +00 at the end). Example
  2. Comparing a timestamp and timestamptz will always result in false.
    1. It makes sense because timestamp is not a real-world point in time. The comparison is technically absurd.
    2. It gets even more confusing because, if you perform EXTRACT(EPOCH FROM <timestamp>), they will both produce the same value.
\n

In order to fix it, we have to convert timestamp back to timestamptz with another AT TIME ZONE 'UTC' like below:

\n
ON a.month_start = ((b.month_start AT TIME ZONE 'UTC') + INTERVAL '1 months') AT TIME ZONE 'UTC'
\n

Now the equality will work as you expect it to work.

","headings":[]}}