Обсуждение: timestamptz, local time in the future, and Don't do it wiki
Hi, I am unsure what is the proper mailing list to discuss an the issue, this one or pgsql-doc. PostgreSQL has a reputation of software with excellent support of time zones, so some people take recommendation to use "timestamp with time zone" type excessively literally. I mean the "Don't do this" page in the wiki: https://wiki.postgresql.org/wiki/Don%27t_Do_This#Date.2FTime_storage Could you, please, add a case when the timestamptz type should not be used? UTC timestamps of forthcoming events may change due to an updates of tzdata if they really scheduled at specific local time. An example: http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html Lau Taarnskov. How to save datetimes for future events - (when UTC is not the right answer) I faced a similar issue once. One day I was asking myself looking at the time displayed by a web application - Was this event scheduled in proper time zone or in UTC? - Was the time zone database updated? I do not know particular reason but the time was wrong. It was a morning just after change of time zone offset. Various changes of time zone offset are not really rare, see https://github.com/eggert/tz/blob/main/NEWS However I am unsure what should be recommended instead of timestamptz. A column for timezone identifier in addition to local time as string is an option. Unfortunately there is an issue with ambiguous local time. I noticed a suggestion to use time zones abbreviations: https://www.postgresql.org/docs/current/datetime-invalid-input.html "B.2. Handling of Invalid or Ambiguous Timestamps" Some problems: - tzdata update may include changes of abbreviations - it is necessary to determine these abbreviations somehow Examples: - Prior to abbreviations like "+08" some letters were used for the same timezones. - Abbreviations may be changed between storing the event and scheduled time. The reason is the same. Offset of standard time changed, so timezone database is updated: Europe/Kyiv Sat Sep 23 22:59:59 1989 UT = Sun Sep 24 02:59:59 1989 MSD isdst=1 gmtoff=14400 Europe/Kyiv Sat Sep 23 23:00:00 1989 UT = Sun Sep 24 02:00:00 1989 MSK isdst=0 gmtoff=10800 Europe/Kyiv Sat Sep 28 23:59:59 1991 UT = Sun Sep 29 02:59:59 1991 EEST isdst=1 gmtoff=10800 Europe/Kyiv Sun Sep 29 00:00:00 1991 UT = Sun Sep 29 02:00:00 1991 EET isdst=0 gmtoff=7200 Let's assume that an appointment was created in 1989 or 1990 before tzdata update '1991-02-30 02:30:00' 'Europe/Kyiv' 'MSK' (or +03:00) with hope to get time after time jump. When new rules was applied attempt to convert the same local time to UTC would give time moment before time transition. In this sense Python's approach with fold=0 or fold=1 looks more straightforward, but I have not idea how to do it in Postgres: >>> from datetime import datetime >>> from zoneinfo import ZoneInfo >>> zi = ZoneInfo("Africa/Juba") >>> "{:%F %T %z %Z}".format(datetime(2021, 1, 31, 23, 30, 0, tzinfo=zi, fold=0)) '2021-01-31 23:30:00 +0300 EAT' >>> "{:%F %T %z %Z}".format(datetime(2021, 1, 31, 23, 30, 0, tzinfo=zi, fold=1)) '2021-01-31 23:30:00 +0200 CAT' Details for this thime transition: zdump -v Africa/Juba | grep 2021 Africa/Juba Sun Jan 31 20:59:59 2021 UT = Sun Jan 31 23:59:59 2021 EAT isdst=0 gmtoff=10800 Africa/Juba Sun Jan 31 21:00:00 2021 UT = Sun Jan 31 23:00:00 2021 CAT isdst=0 gmtoff=7200 It would be great to learn a better approach for appointments in local time in general and for treating of local time ambiguity in particular. I have some links, but they are more to state the problem than to present a robust solution. I do not think they are suitable for the wiki section. To summarize, please, document timestamps in the future as an exception from the "use timestamp with time zone" rule. It would be nice to recommend a better approach to ensure correct local time despite changes in tzdata.
On 2023-01-27 19:12:08 +0700, Max Nikulin wrote: > I am unsure what is the proper mailing list to discuss an the issue, > this one or pgsql-doc. > > PostgreSQL has a reputation of software with excellent support of time > zones, so some people take recommendation to use "timestamp with time zone" > type excessively literally. I mean the "Don't do this" page in the wiki: > https://wiki.postgresql.org/wiki/Don%27t_Do_This#Date.2FTime_storage > > Could you, please, add a case when the timestamptz type should not be used? > UTC timestamps of forthcoming events may change due to an updates of tzdata > if they really scheduled at specific local time. An example: Yes. I could argue that this isn't really a "timestamp", though. The time when the future event will happen isn't fixed yet - it depends on future decisions (e.g. an update to DST rules or even a complete switch to a different time zone). However, few people will be that picky in their terminology. So it's probably a good idea to point out that times which are supposed to be relative to a specific time zone should be stored as local time + time zone, not timestamptz (the time zone can be implicit). hp -- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
Вложения
On 28/01/2023 01:03, Peter J. Holzer wrote: > On 2023-01-27 19:12:08 +0700, Max Nikulin wrote: >> https://wiki.postgresql.org/wiki/Don%27t_Do_This#Date.2FTime_storage >> >> Could you, please, add a case when the timestamptz type should not be used? >> UTC timestamps of forthcoming events may change due to an updates of tzdata >> if they really scheduled at specific local time. An example: > > Yes. I could argue that this isn't really a "timestamp", though. The > time when the future event will happen isn't fixed yet - it depends on > future decisions (e.g. an update to DST rules or even a complete switch > to a different time zone). Generally I would agree that another term instead of timestamp may help to explain people that timestamps in the future are more complex than they usually expect. Do you have an idea what word should be used instead? Interestingly EcmaScript proposal for `Temporal` (intended to fix issues with `Date` objects) avoids using of "timestamp". "Instant" is chosen instead and the reason is confusion caused by discrepancy of interpretation by various databases: https://tc39.es/proposal-temporal/docs/ambiguity.html#understanding-clock-time-vs-exact-time "Understanding Clock Time vs. Exact Time" in "Time Zones and Resolving Ambiguity" By the way, approach chosen for JavaScript is even more versatile than the one implemented in Python. Instead of just `fold={0,1}` `datetime` object field, conversion of local time + TZ identifier may be controlled by `disambiguation={earlier,later,compatible,reject}`. If time string contains both offset and TZ identifier than another options are available: `offset={use,ignore,prefer,reject}`. However timestamp property in some cases may be convenient as well. https://peps.python.org/pep-0495/ "PEP 495 – Local Time Disambiguation" What I do not like with distinction of known UTC time vs. UTC time that might change is that in real applications the difference might be quite subtle. The same event, e.g. a conference with local and remote (on-line) participants may be scheduled in both ways: in respect to local time zone and with fixed UTC time. Moreover the committee may reconsider their early decision. I am in doubts if it is reasonable to use 2 tables with different column types (timestamptz for UTC or strings for local time and TZ) and to move entry between these tables just because time zone is changed from fixed UTC offset to a location-based one or vice versa.