Footguns with Postgres “at time zone 'UTC'” | Hacker News Reader

Footguns with Postgres “at time zone 'UTC'”

bookofrevenue.com

166 pointsbirdculture112 comments

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.

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 two most difficult things in software engineering...

Naming.

Timestamps.

And of by one errors

You are off by one characters.

In my freshman Pascal class, I quickly learned how to use “iff” [if and only if] in a sentence, and my T.A. loved that.

You must not have invalidated your cache since a third thing.

Obviously there are more but the original joke circa 1999 was this. Made Y2K even more exciting.

> but the original joke circa 1999 was this.

No, the original joke, which GP is referring to, is “There are only two hard things in computer science. Naming things and cache invalidation.”

It predates 1999 and the Y2K bug by a fair amount. I first saw it on Usenet in the early 90s, around 1994 I think.

> There are only two hard things in computer science. Naming things and cache invalidation.

More like

  There are only two hard things in
  computer science. Naming, cache
  invalidation, and off-by-one bugs.

too, parallel processing!

Timestamps aren't hard. It's almost everything else with time that's hard.

For examples check : "Falsehoods programmers believe about time..."

Have we solved cache eviction?

It made sense when databases and programs were used almost exclusively locally. It still makes sense for local apps (e.g. local-first or local-only smartphone and desktop apps) who typically will automatically do the right thing that way based on the OS regional settings.

It only started causing widespread issues with the rise of cross-region internet SaaS. Database systems, language runtimes, and OS APIs are keeping the default behavior for backwards compatibility.

Yes and no, it was thought to make sense for "end-user-programmers" where it's helpful to be fully locale specific, I'm Swedish and my OS settings makes programs expecting comma (,) signs for decimal separation is something that's actually hit me today when copy-pasting between programs.

So in practice, while it was kinda useful to be locale/region dependant for some users it's probably been more trouble in the long run to be overly helpful.

C# was released after y2k, so they don't have the excuse.

Also, You're missing the biggest sin here however, locale specific time is OK, automatically allowing conversions/comparisons to points in time types without specifying timezones has in principle never caused anything but grief.

It's not even global, is it? It's session scoped.

Author here

Wow, 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.

This is one of those moments that makes me wonder how anything ever works at all

Adding a month is a footgun even without time zones. I was checking a spreadsheet against another implementation month by month and found that HyperFormula 3.4 returns Feb 28 for EDATE(Jan 31 2028, 1). Excel, Google Sheets and Numbers all return Feb 29, because 2028 is a leap year. Jan 31 + 1 month in a leap year is now a test case in anything I write that does month math.

Thank you for pointing this out with HyperFormula! Appreciate you testing in various environments and sharing the results. I've confirmed the issue and submitted a PR with a fix that should be in the next release.

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

Has anyone proposed versioning timezones? Or is this such an edge case it would be overkill? (Either specify your future instant in UTC if you mean to stick to that, or specify it in a timezone and accept that it could change before it happens, or if you need something else get it in a contract and don't trust the computer!)

It’s really not clear what you’re asking.

The point of using zoned events is to match the life and expectation of people living in the real world e.g. if there is a meeting in Perth at 10AM, and the Perth timezone gets shifted, the meeting is still occurring at 10AM perth time. If it’s broadcast then every other time is what changes (or not).

If people want to fix their meeting internationally they can already do that by setting their meeting time in UTC.

In reply to

> 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)

I wondered if it would be worth applying a (optional?) version to a timezone when stored, so you could distinguish between "whenever it is this time in Perth" vs "when I currently think this time will be in Perth, though if Perth changes its mind on how it offsets time, I want to keep what time I currently think that will be".

But you're right, that doesn't really add anything over storing it as UTC.

The fix is storing the datetime without a timezone, so the hour stays stable, no matter what the timezone of Perth does, plus optionally and separately the location (Perth), which could be stored as the timezone id of the location. But you could also use coordinates. Anything which can be mapped to its timezone later works. Now the time will never change for the original location (sic), and you can always compute the time for any timezone in the world, because in the instance of computation, you take the current valid timezone of the location, and you apply that to the stable datetime. Of course a current computation of that might have a different value in the future. But that doesn't matter, since the time for the original location (sic) never changes, and the value for different timezones is supposed to change.

> if there is a meeting in Perth at 10AM, and the Perth timezone gets shifted, the meeting is still occurring at 10AM perth time.

The thing about this is that if Perth's time zone ever changes so erratically or with such little notice that participants need to be notified that the point-in-time of an upcoming meeting has changed, it's no longer clear whether the participants would actually want the zoned "Perth at 10am" time to be canonical.

If athletes were flying in from around the world for an international competition tomorrow at 10am, and Australia decided to increase Perth's UTC offset by 1 hour as of today, would we expect all athletes, organizers, fans, etc. to show up and do everything 1 hour earlier (by solar time)?

> The thing about this is that if Perth's time zone ever changes so erratically or with such little notice that participants need to be notified that the point-in-time of an upcoming meeting has changed

"erratically" and "with little notice" are pretty relative when it comes to timezone shifts. DST decisions have been made with as little as a week lead time, Samoa dropping an entire day off of its calendar was done with under a year lead time (noises started about 9 months prior, the act was assented 6 months prior).

> would we expect all athletes, organizers, fans, etc. to show up and do everything 1 hour earlier (by solar time)?

Usually yes.

There is actually a simple heuristic you can use: if a point in time should be sticky to a calendar (e.g. calendar app or appointments which need to be synchronized between multiple humans or parties for a given context/location/region), store a datetime _without_ a timezone and make the timezone configurable for the user/infer it from the user. If you want a point in time which will not "physically" change, store a datetime _with_ a timezone, always, preferably UTC (e.g. logging, timers, measuring the occurrence of events).

What’s the difference between these two other than how the client would convert to display to a user?

It's not only about display. If you store a user's appointment only as a UTC timestamp, you actually can't know the hour of the day (and the day itself to be precise) on which this appointment should happen, for a given calendar (probably the user's calendar, in a specific non-UTC timezone). You would have to guess by using the calendar's/user's timezone and compute some offset with UTC. But what if the user changes timezones or the timezone itself changes its value? Store without a timezone, and you know the exact hour and day the user intended. One is pointing to a day and hour in a calendar, the other is pointing at a point on the line of a linear timeline.

You're still just talking about client conversion on the write instead of the read. A Unix epoch is a UTC timestamp. That number is not time-zone-less it's just in the default computer time zone.

I'm referring to a "datetime", which should be a data type which stores a date, e.g. "2026-09-28", and a time, e.g. "16:58:18". The unix epoch is not relevant here, unless you _want_ to store a UTC timestamp. You could store a UTC timestamp also as ISO 8601, which is not a number. But this is not related to the timezone-less datetime I'm talking about. To make my points more clear, just imagine the datetime is stored as a string "2026-09-28 16:58:18" with no implied timezone whatsoever.

But there is zero use case difference between these two things you mentioned:

> if a point in time should be sticky to a calendar store a datetime _without_ a timezone

> If you want a point in time which will not "physically" change, store a datetime _with_ a timezone

Both of these things are exactly identical. You are still storing an exact point in time in both cases. The only thing about a calendar use case is the presentation layer.

> You are still storing an exact point in time in both cases.

No this is not correct. You store two different intents. Suppose you want a reminder in your calendar every day at 15:00 for the next 7 days. It should not change if DST changes in the middle of the seven days. It should not change if legislation of my timezone changes. How do you encode this? A UTC timestamp for each of the seven days will change the hour if the DST changes or the timezone changes. So what you can do is store a datetime _without_ a timezone, exactly reflecting what the user entered when creating the calendar entry, which is "2026-09-29 15:00:00", "2026-09-30 15:00:00", etc. This is the intent wanted by the user, which is a "sticky" time in their calendar. These are not exact points in time, since timezones can and will change (DST), so the "physical" instant of 15:00 can be a different "actual" (as in, the sun has a different position, earth rotation is different) time when comparing the days. Instead, these are exact times in 7 days only in a (specific) calendar.

That system you described is quite rare - it would have to poll N databases of N possible events constantly to see if it should give you a reminder. Which is why none of the common calendar systems do that.

They just create events, which have a time zone and represent and exact point in time. And they don't change the event or when you get notified when you change time zones.

The 2 different types of datetimes encode different information and is not purely a display/presentation issue, it's also storage issue.

I happen to call them "scientific datetime" vs "cultural/political datetime". However, the software dev industry has not converged on a standard vocabulary to delineate the 2 types which is unfortunate because that means programmers are unaware that the difference exists. Concepts are more top-of-mind when there are good names to label them.

If a programmer doesn't understand how the 2 datetimes behave differently, they will create software bugs as I've outlined before: https://news.ycombinator.com/item?id=39418897

There's the meme of "store UTC everywhere" (maybe perceived as correct because of superficial similarity to "use UTF-8 everywhere") ... but storing datetimes as UTC is only unambiguous for historical events such as timestamps of activity stored in server logs.

But future datetimes can have ambiguous edge cases which causes the split into 2 different types.

What you call "cultural/political datetime" should be "standard time" (or maybe "civil time"):

https://en.wikipedia.org/wiki/Standard_time

But this is still different to a time someone enters into a calendar. Standard time can change its offset (to UTC) over time (e.g. DST), while a time in a calendar is fixed in the nominal sense.

"scientific datetime" is quite ambiguous, since I would consider science-level precision time to be TAI (International Atomic Time, what UTC uses as a reference), or maybe UT1, which is one variant of UT (Universal Time, unrelated to UTC), depending on the scientific field. For simple cases, UTC might be enough, so you could call this "UTC".

I think practically what matters for developers are three things:

- Standard time (dependent on timezone)

- UTC (the reference for standard times in the different timezones)

- Calendar times (seems to be called "floating time" [0]), just referring to a specific date and time, usually independent from both standard time and UTC, from the author's perspective (others viewing a foreign calendar might see times interpreted in their own timezone). Often scoped by physical location, but not necessarily.

[0]: https://www.w3.org/TR/timezone/#floating-times

, while a time in a calendar is fixed in the nominal sense.

The above scenario of fixed time regardless of DST/TZ changes is what I tried to call "cultural/political time". In other comments, I called it "appointment time".

What you call "calendar time", others will call it "time with calculated UTC offset". (Which then leads to more meta discussion of "no... calendar time is not UTC offset because ..." )

Both examples of our ambiguous labels causing more confusion is prime example of the industry not converging on good names to make devs aware of the difference.

>I think practically what matters for developers are three things:

That categorization is fine but is still obscuring the key issue: many developers think they can collapse all of your 3 types into one simple strategy of "always store it as UTC"

The trouble is that you can't really make safe assumptions about whether to use the "sticky" paradigm or the "point-in-time" paradigm.

Sticky really only makes sense in two scenarios:

1. when all participants are assumed to be in the same geographic/political time zone for the foreseeable future (in which case the only advantage over point-in-time timestamps is future political changes to that region's time zone, like DST changes), or

2. when there's some privileged participant such that everyone else can assume events follow that participant's time zone (e.g. a company headquarters that moves very rarely, or an individual's personal wakeup alarms which can probably be assumed to follow their current location's time zone as they travel).

If you have a group of friends who like to stay in touch with regular group calls, and all/most of them are digital nomads who change their time zone of residence multiple times per year, you probably don't want the sticky paradigm.

In practice, if you have participants from different timezones, you agree on one "reference" timezone, which can be also UTC, as in your nomads example. You can consider UTC as just another calendar you can stick to (but you should just not hardcode this in software for this appointment use-case). You actually need a reference to be able to plan an event in the first place. Otherwise you don't know which time you can propose. You would need to propose a "fixed" time for every timezone which participates, but that doesn't work, because these times might not refer to the same physical instant, so the meeting would not be (fully) synchronized. So instead, you take some time from some timezone and translate that to all the other timezones. You can even see that in online games, where some of them have an official "server time", which is globally the same, helping players to meet at the same time instant. UTC itself is another examples of this, used e.g. for global navigation or air traffic control.

That "only advantage" is doing a lot to dismiss the actual use case of, say, everyone keeping appointments on their calendar when the legislature passes laws around time zone changes.

> If you want a point in time which will not "physically" change, store a datetime _with_ a timezone, always, preferably UTC (e.g. logging, timers, measuring the occurrence of events).

This works for immutably recording the current time into a log, yes.

For much else (e.g. a timer still-to-come that should go off in “1000 days”), leap seconds break this.

You could store such time using TAI as the timezone (TAI is UTC without the leap seconds), if RDBMSes actually persisted the timezone. But they don’t. They’ll just convert back to UTC at point of write.

I have a feeling that most people who really need to solve this problem end up using a (pos, len) column pair where `pos` is the current UTC time when the future-event was registered, and `len` is an interval representing how far away it is in monotonic time — either as a difference of POSIX timestamps at time of evaluation, or as a SQL INTERVAL, etc.

tzdb is versioned, but I can't recall if the individual timezones carry a version though...

One way to manage is to store the datetimes with a timezone identifier and the offset, and when you load a new tzdb, go through and validate that the calculated offset matches the stored offset... for those events where they don't match, you have an exciting challenge of figuring out if the event should stay with the time zone or stay with the offset; both answers may be right ... ideally you inform the user(s) about what you've done and allow them to fix things software has messed up.

JS’s temporal API will serialize zoned datetimes with both offset and timezone (specifically, an RFC 9557 representation), this can be used to infer tzdb updates. By default, temporal will raise an error when parsing a datetime where the offset and the timezone’s offset differ, but it can be configured to sway one way or the other depending on use case.

A java.time.ZonedDateTime is not simply zoneid+date+time.

It has the zone offset and so is completely unambiguous and invariable.

  private final LocalDateTime dateTime;
  private final ZoneOffset offset;
  private final ZoneId zone;

Converting Instant+ZoneId into a ZonedDateTime can vary when zone rules change. The inverse does not.

Still, the GP is correct about the problems.

Relational databases have exactly 1 type that corresponds to modern data-handling practices: timestamp with time zone, that stores a timestamp. There is no good way to store any other modern type, and the 1980s practices on time handling weren't actually very good.

I'd say relational databases (in the sense of standard SQL) have 0 types that correspond to modern data-handling practices: timestamp with timezone stores an instant but lies about it and implicitly gets converted from and to the connection-local timezone.

Agreed. It's a major pain in the butt, and I wish I could just ship an int64 back and forth and not have to deal with mapping it to a calendar.

It's always worth noting that future UTC timestamps are also ambiguous for certain operations, most notably computing durations, due to the unpredictability of leap seconds.

SET TIME ZONE 'UTC'; // or GMT, PST, etc.

Except you shouldn't do the GMT/PST part, because it can lead to people falsely believing that Postgres can handle time zones, leading to silent data corruption when the time zone definition changes - such as due to abolishing DST.

Postgres can only store 1) unzoned local time, or 2) UTC, with optional conversion on read/write.

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

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.

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 in non-UTC TZs for user input (e.g., in a calendaring app).

My bible: https://wiki.postgresql.org/wiki/Don't_Do_This

> 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 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 opens at 7:30 AM and closes at 7:30 PM Eastern time (ET) closes on different UTC date than it opens when ET is EST but opens and closes on the same date when ET is EDT, so it’s plausible that the dates actually needed to be updated.

Daylight saving changed the UTC time, because opening hours were always in local time.

I don't know the exact use case, but it sounds weird. If UTC is the single source of truth, then when you display the data and want to change some business assumptions, you only need to change the application logic. The underlying data remains unchanged.

One scenario I can imagine is when you want to store the results of business calculations in the database. In that case, once the algorithm changes, you may also need to update the stored data. This may be necessary for performance reasons.

In all other cases, calculating the result on the fly solves the problem and does not require a database update when the business rules change.

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.

It only works backwards (though you can probably get away with it if your future dates are not too far forward). There are ~10 tzdb updates a year due to rule changes and if you bake a date on the other side of one to UTC too early then your app is going to have incorrect datetimes

So how are you solving the example in the article where they join on timestamps? Read both tables from the DB into the application?

I read raw data (UTC timestamps) from the db and timezone for user profile and calculate it on the fly.

We can think of the split between DB and application in terms of DX or code, or we can think of it in terms of colocation of data and compute. The latter case will be compelling sometimes.

I've seen too many ways storing everything as UTC goes wrong/gets confusing. Round-tripping at least UTC offset (which unfortunately Postgres `timestamptz` in this case does not do and is effectively the same as storing as UTC) gives you more debugging tools for events in the past and more opportunities to do the right thing for future events in worst cases (strange, unexpected DST shifts). The best option is if you can also round trip exact time zones such as IANA strings like "America/Los_Angeles". Even fewer databases support that natively right now (as a single optimized column).

When UTC offsets roundtrip you can do all your date math as if everything was in UTC, but still not lose information from the user about what time they thought an event occurred at or might next occur at.

t depends on the system. Keeping a time zone alongside a UTC timestamp may be useful in some cases.

For example, if your system is supposed to remind a user to do something, such as take a pill, and the user changes time zones while travelling, you may want the reminder to occur at 9:00 local time wherever they currently are. In that case, storing the original time zone together with the event may not be necessary, because the relevant time zone is the user's current one.

Everything depends on the context. For me, however, there is no doubt about one thing: I store timestamps in UTC. Whether I also store a time zone, and where I store it, depends on the system and its business requirements.

Keeping a UTC offset is two extra bytes and with a UTC offset you can do all of your time comparisons and time math as if everything was in UTC, but you have extra debugging information in the UTC offset. It's still sometimes useful to store a time zone as offsets to timezones certainly are not 1:1 (esp. with DST math), but some places like logs and past dates UTC offset is sufficient and you don't even need to store timezone.

Postgres doesn't a native type that supports UTC offsets, but some other databases do and it is extremely useful. At this point in my career, I would never choose UTC storage over UTC Offset storage.

Ruby on Rails would agree with you. I've been working in web development for a decade now, across a couple of languages and frameworks, and I've only ever stored UTC. I currently work a lot with time-series data, and I'd rather not know what hack makes it possible to track events that occur during a DST transition.

> 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).

D. Richard Hipp had a great blog about this on sqlite.org quite a while back.

If you have a link that would be appreciated.

Looking... I can't find it with Google, but https://www.sqlite.org/lang_datefunc.html mentions it:

| Because the length of a month or year changes from one month or year to the next, ambiguities can arise when shifting a date by months and/or years. For example, what is the date one year after 2024-02-29? Is it 2025-02-28 or 2025-03-01? Or what is the date that is two months after 2023-12-31? Is it 2024-02-29 or 2024-03-02? There is no consensus on how to resolve this ambiguity, so the "ceiling" and "floor" modifiers (14 and 15) are available to let the programmer decide. If the next modifier after a time shift is "ceiling", then any ambiguity in the date is resolved by choosing the later date. The "floor" modifier resolves ambiguities by resolving to the last day of the previous month. The default behavior is "ceiling".

It’s not just a matter of there being different ways to do it, it’s also that none of the ways obey expected arithmetic laws like associativity. Adding four months and then subtracting four months can yield a different day than the starting point. Or adding three months and then one month can yield a different date than adding four months at once.

> For example, what is the date one year after 2024-02-29?

2025-03-01

> Or what is the date that is two months after 2023-12-31? Is it 2024-02-29 or 2024-03-02?

2024-02-29

But better use units of fixed size, if you do not want trouble.

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

    -- TIMESTAMPTZ AT TIME ZONE 'X' -> returns TIMESTAMP in timezone X
    -- TIMESTAMP AT TIME ZONE 'X' -> returns TIMESTAMPTZ treating input as timezone X

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 NULL in the timezone slot. I think it should mean "unknown, anything goes on a per-value basis!", rather than treating it like some polymorphic type that gets magically parameterized at runtime.

IMHO, it is a mistake of the standard and PostgreSQL authors to try to enable such sloppy thinking by users and applications. Ordering relations shouldn't even be implemented on naive timestamps. It should be a type error, not a trigger for implicit coercion.

Perhaps it should even be a domain over text or some composite type that represents the partially populated time info. Require explicit mutation to populate the missing bits and allow conversion to a well-defined moment.

I think a sane application should only use the timezone-aware timestamp for storage, and explicitly manage its own "timestamp templates" and conversions before trying to do comparisons on the timeline.

Edit to add: I think you can say the same about timeline versus some timestamp-with-timezone strings. Make it more explicit that the ordered type is normalized moments. Make sure there is a normalizable external representation like ISO timestamps.

Make it clear that other representations are not stable. E.g. any legal timezone that could have its definitions change over time is not a stable concept to use in a representation of a moment. It is also effectively naive unless it includes another version parameter to state which version of the legal definition is intended.

I don't know, I find:

>AT TIME ZONE 'UTC' converts the data type from timestamptz to timestamp

surprising and a foot gun. Asking for at a timezone stripping the timezone makes no sense to me. Having `AT TIME ZONE 'UTC'` produce a timestamptz at +0:00 seems not insane.

Yes, the SQL syntax is full of misnomers. I agree it should have more hazard tape around it.

The type qualifier "with timezone" doesn't mean it stores a timezone with it! It means the input is interpreted with timezone offset, producing an unambiguous moment on the timeline. You can then compare all such values with a total ordering. But, these stored values do not preserve any offset/locale information. You can't ask "what was the offset of this timestamp when it was input?" That denormalized locale information is stripped when it is interpreted.

The naive type (without timezone) really means that the timezone information is absent and the interpretation is to be deferred. You have to supply timezone information before it can be resolved to the timeline. This is what the implicit coercion is doing in PostgreSQL, mixing in either the session or server timezone offset.

The above is further complicated in that PostgreSQL will supply an implicit (session or server) timezone during interpretation of the input for timestamp with time zone. And conversely, it will ignore timezone or offset even if present in the input for a naive timestamp! I think both of these would be better off handled as type/input errors in a strict mode.

The poorly named "(ts)::timestamptz at time zone 'tz'" construct is the inverse of the input transform that takes a naive timestamp and the given timezone to produce the known moment.

The other sad bit is that all of this is naive about the difference between UTC, TAI, and Unix time standards. There is ambiguity in postulating any future time, since the exact presence of leap seconds is not yet determined.

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*".

There are both variants in the wiki:

https://wiki.postgresql.org/wiki/Don't_Do_This#Don't_use_tim...

> Don't use the timestamp type to store timestamps, use timestamptz (also known as timestamp with time zone) instead.

> Why not?

> timestamptz records a single moment in time. Despite what the name says it doesn't store a timestamp, just a point in time described as the number of microseconds since January 1st, 2000 in UTC. You can insert values in any timezone and it'll store the point in time that value describes. By default it will display times in your current timezone, but you can use at time zone to display it in other time zones.

> Because it stores a point in time it will do the right thing with arithmetic involving timestamps entered in different timezones - including between timestamps from the same location on different sides of a daylight savings time change.

> timestamp (also known as timestamp without time zone) doesn't do any of that, it just stores a date and time you give it. You can think of it being a picture of a calendar and a clock rather than a point in time. Without additional information - the timezone - you don't know what time it records. Because of that, arithmetic between timestamps from different locations or between timestamps from summer and winter may give the wrong answer.

> So if what you want to store is a point in time, rather than a picture of a clock, use timestamptz.

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 timestamp

- 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)

SQL Server has `datetimeoffset` which stores the UTC offset (and so roundtrips it), which is definitely an improvement. (You still may need/want a string timezone column even with UTC offsets, but you don't need a denormalized _utc column because datetimeoffset math just works as if the times were all UTC.) It seems like something that Postgres could use. Especially because the overhead for storing UTC offsets isn't that much. (10 bytes versus 8.)

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.

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.

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?

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.

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

If the UTC is derived why store it (unless just as a performance concern e.g. materialised view type of thing)

Presumably if the conversion logic changes. Eg user enters a future date in a place but the timezone in that place changes in the future.

That's a good point although I would prefer to have a deterministic conversion that handles historic changes. But I get the point and having a source of truth for the conversion could be better as it won't be affected by bugs in the conversion of deemed right. It does mean maintaining two values though.

> '2026-02-28 16:00:00-08'::timestamptz AT TIME ZONE 'UTC'

Er… just write '2026-02-28 16:00:00-08Z'::timestamptz.

- blog page not working?

- https://bookofrevenue.com/blog/

I didn't know trailing slash didn't on my website. I'll fix this. Thank you for pointing it out.

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

Different take. Maybe the mistake is believing you can add "1 month" to anything and expecting your intuition to be worth anything.

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.

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.)

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.

People who handle this footgun properly tend to come from two groups:

- Those with safety training for this class of foot-pointed firearms, who know how dumb shit looks like and that they should not do it;

- Those with officer training who are able to recognize the higher-level categories, and take principled approach to safety - e.g. recognizing that "date", "timestamp, "duration", "time of day", "time of week", etc. are different concepts and should not be mixed.

Reply on news.ycombinator.com