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).
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: <a href="https://news.ycombinator.com/item?id=39418897">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.
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.
, 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"
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 ↗
jasode · · focus · HN ↗
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: <a href="https://news.ycombinator.com/item?id=39418897">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.
pseidemann · · focus · HN ↗
<a href="https://en.wikipedia.org/wiki/Standard_time" rel="nofollow">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]: <a href="https://www.w3.org/TR/timezone/#floating-times" rel="nofollow">https://www.w3.org/TR/timezone/#floating-times
jasode · · focus · HN ↗
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"