Re: Inconsistency of timezones in postgresql

> On Aug 1, 2024, at 21:55, Chris BSomething <xpusostomos(at)gmail(dot)com> wrote:
> It says that it assumes that the "value is in the NAMED timezone". What actually happens is it assumes the value is in your environmental time zone, and DISPLAYS it in your current zone.

No, that's not correct. There are two separate conversions happening: first, it converts the TIMESTAMP value to a TIMESTAMPTZ value. In doing so, it assumes TIMESTAMP value represents a timestamp at the time zone specified with AT TIME ZONE. Now, you have a TIMESTAMPTZ, which (internally) is in UTC. When that is displayed, it's converted to the session timezone.

xof=# select '2024-01-02 00:00'::timestamp;
timestamp
---------------------
2024-01-02 00:00:00
(1 row)

xof=# show timezone;
TimeZone
------------
US/Pacific
(1 row)

xof=# select ('2024-01-02 00:00'::timestamp) at time zone 'US/Pacific';
timezone
------------------------
2024-01-02 00:00:00-08
(1 row)

xof=# select ('2024-01-02 00:00'::timestamp) at time zone 'UTC';
timezone
------------------------
2024-01-01 16:00:00-08
(1 row)

xof=# set timezone = 'UTC';
SET
xof=# select ('2024-01-02 00:00'::timestamp) at time zone 'UTC';
timezone
------------------------
2024-01-02 00:00:00+00
(1 row)

This can indeed be confusing, but it works as documented.