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.
