On 14/01/2023 22:05, Ihor Radchenko wrote:
<to...@tuxteam.de> writes:
Now there's still enough work for the applications to do: presentation,
parsing, disambiguation, if necessary asking the user for help. Someone
mentioned PostgreSQL -- this is a nice example of what can be done beyond
the (comparatively!) boring details of time zone management :-)
Do I understand correctly that PostgreSQL insists on using timestamps
with time zone info in favour or ordinary timestamps? Exactly to avoid
issues with the same timestamp pointing to a different real time from
epoch depending on the current OS time zone setting?
My points of what that wiki page tries to say:
PostgreSQL has better support of timezone conversions than some of other
databases or middleware libraries. That is why taking advantage of the
postgres feature allows to create more reliable application. The recipe
is to allow postgres to store timestamps in UTC and to delegate it time
conversion by specifying "timestamp with time zone" type.
"With time zone" does not mean that original time zone is stored. It is
converted to UTC from explicitly specified timezone or from the one set
for client's connection.
Some postgres time related types was added just because they are
required to pass SQL compliance tests or to allow easier migration from
other databases. They should be avoided when possible.
I totally agree with the recommendation to use timestamptz for data
related to something in history: billing, bank transactions, etc.
However it is call to trouble for planned events and schedules. Not
frequent, so almost untested use cases.
If I remember correctly, another type, timestamp (without tz), may be
cast on demand to any timezone (stored in another column or passed from
application). So it may be used obtain correct UTC timestamp after
update of timezone database.
Thinking more about this, I can see how it can be important for
clocking, and similar auto-recorded information. Users may be surprised
to record clocking on some task yesterday just to find the clocking data
in future upon travelling from Singapore to San Fran.
In the case of crossing international date line it is certainly an
issue. Users often crossing of timezone border with several hours time
difference may need to specify timezone explicitly as well. For others
timezone change history should be enough to calculate duration of
clocking intervals from regular local time.
