pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedtimestamptz, local time in the future, and Don't do it wiki
3+ messages / 2 participants
[nested] [flat]
* timestamptz, local time in the future, and Don't do it wiki
@ 2023-01-27 12:12 Max Nikulin <manikulin@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Max Nikulin @ 2023-01-27 12:12 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: timestamptz, local time in the future, and Don't do it wiki
@ 2023-01-27 18:03 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: Max Nikulin <manikulin@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Peter J. Holzer @ 2023-01-27 18:03 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: timestamptz, local time in the future, and Don't do it wiki
@ 2023-01-31 11:32 Max Nikulin <manikulin@gmail.com>
parent: Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 0 replies; 3+ messages in thread
From: Max Nikulin @ 2023-01-31 11:32 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-01-31 11:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-27 12:12 timestamptz, local time in the future, and Don't do it wiki Max Nikulin <manikulin@gmail.com>
2023-01-27 18:03 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-01-31 11:32 ` Max Nikulin <manikulin@gmail.com>
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