dealing with UTC dates and the future-CodePudding

I just discovered, that storing dates in utc is not ideally correct if we are also dealing with dates in the future. It seems to be the case because, timezones seem to change more often than we think they do. Fortunately, we seem to have the IANA tzdb that seems to get updated periodically, but, confusingly, postgres seems to use a specific version of the db which it seems to have at build time..

So, my question is, if the timezones are changing, with daylight saving going on, with political, geographical adjustments happening, and our database is not with the latest of the tzdb, how would we be able to keep track of the accuracy of the dates in the system? Additionally, would libraries like date-fns-tz basically not be accurate to account for new timezone changes?

Ideally I would think a library would make a network call to a central server that would maintain the latest changes, but, it doesn't seem to be the case. How are the latest date/timezone changes usually dealt with?

CodePudding user response：

The IANA time zone database collects the global knowledge about what time zone was in effect at what time in every part of the world. That information is naturally incomplete, specifically when it comes to the future.

What is stored in the database is always an UTC timestamp, so the timestamp itself is immutable. What changes is the representation. So if you predict that the world will end next July 15 at noon Austrian time, and the Austrian government abolishes daylight savings time, your prediction will be an hour off (unless you expect the cataclysm to follow Austrian legislation). If you are worried about that, make your predictions in UTC or at least add the UTC offset to the timestamp.

If you store the timestamp with time zone in the database, and you query it today with timezone set to Europe/Vienna, you will get a certain result. If you update the time zone database, and the new legislation is reflected in the update, then the same query will return a different result tomorrow. However, it will still be the same timestamp, only the UTC offset in use will be different:

SELECT TIMESTAMP WITH TIME ZONE '2023-07-15 12:00:00 02'
     = TIMESTAMP WITH TIME ZONE '2023-07-15 11:00:00 01';

 ?column? 
══════════
 t
(1 row)

CodePudding user response：

A very interesting observation!

I understand that you want to store a future point in time, like "10:00am on July 5th 2078 in the time zone of Australia/Sydney", regardless of what offset that time zone has compared to UTC when you retrieve the point in time again. And when the time comes, the point in time might not even exist, because it is being skipped for the introduction of daylight saving time (or it might exist more than once).

Speaking XML Schema, the information you want to store consists of

a dateTime without timezoneOffset, in the given example 2078-07-05T10:00:00 (no trailing Z)
plus a time zone, given as a string from the IANA database, in the given example Australia/Sydney.

I don't know how this is best stored in a PostgreSQL database, whether as two separate strings, or in a special data type.