From: Max Nikulin <manikulin@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: timestamptz, local time in the future, and Don't do it wiki
Date: Fri, 27 Jan 2023 19:12:08 +0700
Message-ID: <d32b076e-2f7b-1fd8-5b18-19357fdb6fd4@gmail.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: manikulin@gmail.com
Subject: Re: timestamptz, local time in the future, and Don't do it wiki
In-Reply-To: <d32b076e-2f7b-1fd8-5b18-19357fdb6fd4@gmail.com>
References: <d32b076e-2f7b-1fd8-5b18-19357fdb6fd4@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox