On Wed, Jun 2, 2021 at 7:56 AM Antoine Pitrou <anto...@python.org> wrote:
>
> This seems rather weird to me: timestamps always convey a UTC timestamp
> value, optionally decorated with a local timezone? What is the
> motivation for such a representation? It is unlike other systems such
> as Python
It's standard. I think the motivation is: "local timestamps are the worst
things in computing." (Oh no, here comes a rant!)
SQL gets timestamps completely wrong. MySQL, PostgreSQL, MSSQL and Oracle
all use similar words ("timestamp", "datetime", etc.) to mean different
things. Depending on the RDBMS, you need to think in five timezones --
server timezone, client timezone, database timezone, database-column
timezone and cell timezone. The syntax and semantics are different in all
database engines. (Personally, I always wince at Postgres' "TIMESTAMP WITH
TIMEZONE": it's the best practice because *it doesn't store a timezone*.
All RDBMSs are similarly absurd; props to MySQL for being slightly less
nonsensical than the rest.)
Python is based on C, and C has an obsession with "local time". What an
awful relic. Python `datetime` deals in wildly-inefficient 9-tuples, not
integers; and it happily stores and represents nonexistent times such as
`datetime.datetime(2018, 3, 11, 2, 30,
tzinfo=zoneinfo.ZoneInfo(key='US/Eastern'))`. Python's `time` module gets
you into C-land and integers; there, timezone-aware math only works in the
"local timezone", a global variable read from os.environ["TZ"] and cached
elsewhere in the module.
Local times are *hard to compare* (they jump around daylight savings);
they're *hard to validate* (some don't exist, others are ambiguous); and
they *cannot store future times* (future timezones are yet to be decreed by
politicians).
Don't follow in C or SQL's footsteps. Store timestamps as integers UTC
timestamps. Store timezone somewhere else; use it to convert to local
timezone when formatting and to convert to calendar for calendar math.
--
Adam Hooper
+1-514-882-9694
http://adamhooper.com
