Postgres AT TIME ZONE 'UTC' converts data type unexpectedly
Blog post clarifies that the SQL command converts timestamptz to plain timestamp, not just displaying UTC representation.
What to know
- Postgres's AT TIME ZONE 'UTC' command converts a timestamptz column to a plain timestamp (without timezone), not just displaying the same data in UTC representation.
- The distinction matters for data integrity: converting to timestamp without timezone loses timezone information unless the location is stored separately.
- Discussion reveals broader debate about database timezone strategy: some favor UTC-only storage, others advocate IANA timezones or location-based approaches depending on use case.
The dispute Whether storing UTC-only is a best practice or loses critical information depends on whether location/timezone metadata is stored separately. · positions read across 22 posts and comments
AT TIME ZONE 'UTC' is a footgun because converting to timestamp loses timezone info.
-
“You loose information if you only save utc. If you save the location of the event separately that is fine, but also forces you to do the math manually.”
good_live · Reddit ↗
Use IANA time zones as the default; they're unambiguous and handle conversion/arithmetic.
-
“I wish we'd just collectively start using IANA time zones as a default. Conversion and arithmetic are solved problems for them, and it's the least ambiguous format for stored dates.”
FuckOnion · Reddit ↗
The choice depends on what data you're actually storing; no one-size-fits-all solution.
-
“I would say it always depends on what data you are actually saving.”
good_live · Reddit ↗
tanin47 Blog post author
How it unfolded 4 developments, newest first · click a bar or a number to jump articlespostscomments
-
4
Discussion spreads to Mastodon and social platforms
The post gains further reach as it's shared across Mastodon and other platforms, with commentary on timezone handling and database best practices.
-
Unless I'm really misunderstanding, this is just "footgun of timestamp without timezone and implicit coercion"?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…
2 more of the top 3 · 18 posts in this stretch
-
Yes. I know. I understand this. An Instant is measured against a reference point that has a known relationship to the world's time zones, so you can translate the instant's value to its representation in any time zone. A LocalDateTime has no reference point, so it has no relationship to time zones at all. (It's basically the same as timestamp with…
-
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…
-
-
3
Post reaches Hacker News frontpage
The article circulates on Hacker News under the headline 'Footguns with Postgres "at time zone 'UTC'"', generating discussion among database engineers.
“I wish we'd just collectively start using IANA time zones as a default. Conversion and arithmetic are solved problems for them, and it's the least ambiguous format for stored dates.”
— FuckOnion, Reddit commenter · source -
first by HN Frontpage, 1d ago
-
I wish we'd just collectively start using IANA time zones as a default. Conversion and arithmetic are solved problems for them, and it's the least ambiguous format for stored dates. Offsets are ephemeral whereas IANA time zones are built to last.
-
-
2
Author updates blog with clearer summary
The blog post author modifies the article to add a summary at the top, explicitly stating that timestamptz AT TIME ZONE 'UTC' converts to timestamp without timezone.
“timestamptz AT TIME ZONE 'UTC' gives us a timestamp without time zone i.e. timestamp, not a timestamptz. It converts the data type to timestamp without time zone.”
— tanin47 -
> timestamptz AT TIME ZONE 'UTC' gives you the representation of a moment of time in the UTC time zone I modified the blog to be clearer with a summary at the top. To clarify: `timestamptz AT TIME ZONE 'UTC'` gives us a timestamp without time zone i.e. `timestamp`, not a `timestamptz`. It converts the data type to `timestamp without time zone`.
2 more of the top 3 · 3 posts in this stretch
-
You loose information if you only save utc. If you save the location of the event separately that is fine, but also forces you to do the math manually. I would say it always depends on what data you are actually saving.
-
The TLDR is actually: `AT TIME ZONE 'UTC'` converts the data type from `timestamp with timezone` to `timestamp without timezone`. I've modified the blog post to add the summary at the top!
-
-
1
Blog post details Postgres timezone conversion behavior
A technical blog post explains how Postgres's AT TIME ZONE 'UTC' command works—specifically that it converts the data type from timestamptz to plain timestamp rather than just changing representation. The author clarifies the distinction in response to confusion about the feature's behavior.
What people are saying 14 voices from 1 site · best of 22 · verbatim
- Yesterday
-
I’m having a really hard time understanding this. I don’t work on Postgres so cut me some slack.Apparently both timestamp and timestamptz saves 8 bit integer that represents a datetime in a similar fashion as Unix epoch.Neither of them stores a timezone or an offset.So what are they?
-
This is one of those moments that makes me wonder how anything ever works at all
-
> '2026-02-28 16:00:00-08'::timestamptz AT TIME ZONE 'UTC'Er… just write '2026-02-28 16:00:00-08Z'::timestamptz.
-
In nearly all cases, I recommend people use TIMESTAMPTZ with UTC timezone (i.e. convert on the way in). If possible, operate your apps with UTC until it hits a user's eyes, i.e. internally define your "day" to start in ~Greenwich UK.This saves a __lot__ of headaches, of which timestamp vs timestamptz is the tip of the iceberg.
-
Author hereWow, thank you for testing it. I did test it on my own laptop (Seattle).> date_add(b.month_start, interval '1 month', 'UTC')I didn't know this. Thank you.
-
The problem really is inherent to DST itself, just as the month math in TFA is inherently wonky in any system. What's January 30th + 1 month? February 28th (or 29th, if a leap year)? March 1st? March 2nd?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…
-
Does the same hole exist for range types? If a GiST exclusion constraint on tstzrange is built on the same comparison logic, a booking table could accept two reservations that overlap only inside the DST gap, and nobody would notice until two people show up for the same room. Curious whether you checked that path or only the btree family.
-
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…
-
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…
-
Automatic conversion using "global" state between "local"/"human" time (5pm where I am now) and points in time (ie with timezone) is one of the biggest sins imho for many libraries/db's/languages. Ran into it a bunch of times using C# as well.
-
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…
-
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…
-
> 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).
-
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 ...