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)
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).
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.
ulrikrasmussen · · focus · HN ↗
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).
masklinn · · focus · HN ↗
- 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)
rawling · · focus · HN ↗
pseidemann · · focus · HN ↗
skrtskrt · · focus · HN ↗
pseidemann · · focus · HN ↗
skrtskrt · · focus · HN ↗
pseidemann · · focus · HN ↗
skrtskrt · · focus · HN ↗
> 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.
pseidemann · · focus · HN ↗
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.
skrtskrt · · focus · HN ↗
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.
[deleted] · · focus · HN ↗
[deleted]