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.
