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)
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.
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.
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!)
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).
> 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.
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.
What’s the difference between these two other than how the client would convert to display to a user?
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"
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.
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.
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.
A java.time.ZonedDateTime is not simply zoneid+date+time.
It has the zone offset and so is completely unambiguous and invariable.
Converting Instant+ZoneId into a ZonedDateTime can vary when zone rules change. The inverse does not.Ah, yes, you are right! Forgot about those two details
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.