From: Max Nikulin <manikulin@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: timestamptz, local time in the future, and Don't do it wiki
Date: Tue, 31 Jan 2023 18:32:09 +0700
Message-ID: <d2d75d36-f4bc-3ef0-365a-27993bd3731c@gmail.com> (raw)
In-Reply-To: <20230127180346.pn3gbwaajr2cckg4@hjp.at>
References: <d32b076e-2f7b-1fd8-5b18-19357fdb6fd4@gmail.com>
<20230127180346.pn3gbwaajr2cckg4@hjp.at>
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.
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: <d2d75d36-f4bc-3ef0-365a-27993bd3731c@gmail.com>
References: <d32b076e-2f7b-1fd8-5b18-19357fdb6fd4@gmail.com> <20230127180346.pn3gbwaajr2cckg4@hjp.at> <d2d75d36-f4bc-3ef0-365a-27993bd3731c@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