Probably reflecting some of my confusion at the time (hence my addendum to the post) but I’m still unconvinced that timestamptz is the cure all that the wiki promotes it to be.
It feels like a technological quick fix for a conceptual misunderstanding.
With timestamp without time zone the timezone is portrayed as missing which is entirely true when looking at the type and perhaps even the record in which a value of that type exists. But in truth the timezone is usually implied. The problem is that from an individual point of view it is local time that is perceived and recorded. And in legacy systems that often was the frame of reference.
However from a global point of view you either need an explicit timezone as a frame of reference or you need a fixed frame of reference, i.e. UTC.
So frankly any time represented/stored without a timezone should be UTC in order to avoid confusion. So storing UTC is the part that timestamptz gets right.
it’s not a conversion of the underlying value at all, but rather a guess at a convenient display format.
So the good news is that the UTC value is sent to the querying system. The bad news is that the value displayed in the an admin tool like psql won’t match it unless you set timezone to 'UTC'; - I can see that generating a lot of confusion.
For my local time zone it doesn’t convert 2019-05-10 07:30:00 to 2019-05-10 00:30:00, but rather 2019-05-10 00:30:00-07.
I’m assuming that 2019-05-10 07:30:00 refers to the already stored value, because:
=# set timezone to '-7';
SET
=# select '2019-05-10 07:30:00'::timestamptz;
timestamptz
------------------------
2019-05-10 07:30:00-07
(1 row)
=# select '2019-05-10 07:30:00 UTC'::timestamptz;
timestamptz
------------------------
2019-05-10 00:30:00-07
(1 row)
i.e. a string without a timezone, parsed inside the server is going to assume the set timezone or timezone from the postgresql.conf. But
it’s common practice in distributed system to set it to UTC
guards against that.
Seems don’t put anything but UTC into timestamp is a simpler solution to the problem (which seems to be the Ecto approach - and the “convenience” of timestamptz isn’t needed anyway).
Another problem that timestamptz doesn’t fix - ambiguity during local end of daylight savings (unless server/connection is set to UTC or ignore DS).
=# SELECT '2019-11-03 01:50'::timestamptz AT TIME ZONE 'UTC';
timezone
---------------------
2019-11-03 06:50:00
(1 row)
=# SELECT '2019-11-03 05:50 UTC'::timestamptz;
timestamptz
------------------------
2019-11-03 01:50:00-04
(1 row)
=# SELECT '2019-11-03 06:50 UTC'::timestamptz;
timestamptz
------------------------
2019-11-03 01:50:00-05
(1 row)
