pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.





view thread (3+ messages)

Message-ID: <d2d75d36-f4bc-3ef0-365a-27993bd3731c@gmail.com>
Permalink:  ../d2d75d36-f4bc-3ef0-365a-27993bd3731c@gmail.com/
Also on:    postgresql.org/message-id/d2d75d36-f4bc-3ef0-365a-27993bd3731c@gmail.com

 · 

reply

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