Re: Inconsistency of timezones in postgresql

> On Aug 1, 2024, at 21:55, Chris BSomething <xpusostomos@gmail.com> wrote:
> It says that it assumes that the "value is in the NAMED timezone". What actually happens is it assumes the value is
inyour 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
TIMESTAMPTZvalue.  In doing so, it assumes TIMESTAMP value represents a timestamp at the time zone specified with AT
TIMEZONE.  Now, you have a TIMESTAMPTZ, which (internally) is in UTC.  When that is displayed, it's converted to the
sessiontimezone. 

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.