TIL about TIMESTAMP / timezone conversion in PostgreSQL

[dhermes]

See also https://www.enterprisedb.com/postgres-tutorials/postgres-time-zone-explained

Setup

this_db=> CREATE TABLE foo ( t TIMESTAMPTZ NOT NULL );
CREATE TABLE
this_db=> INSERT INTO foo (t) VALUES ('2023-11-27 16:03:47.654482+00');
INSERT 0 1

Observations in UTC

this_db=> SET TimeZone = 'UTC';
SET
this_db=> SELECT t FROM foo;
               t
-------------------------------
 2023-11-27 16:03:47.654482+00
(1 row)

this_db=> SELECT t::TIMESTAMP FROM foo;
             t
----------------------------
 2023-11-27 16:03:47.654482
(1 row)

Observations outside of UTC (America/Chicago)

this_db=> SET TimeZone = 'America/Chicago';
SET
this_db=> SELECT t FROM foo;
               t
-------------------------------
 2023-11-27 10:03:47.654482-06
(1 row)

this_db=> SELECT t::TIMESTAMP FROM foo;
             t
----------------------------
 2023-11-27 10:03:47.654482
(1 row)

Using AT TIME ZONE '...'

The AT TIME ZONE capability performs the "opposite" function on a TIMESTAMP and a TIMESTAMPTZ. Observe:

this_db=> SET TimeZone = 'UTC';
SET

this_db=> SELECT t AT TIME ZONE 'America/Chicago' FROM foo;
          timezone
----------------------------
 2023-11-27 10:03:47.654482
(1 row)

this_db=> SELECT t::TIMESTAMP AT TIME ZONE 'America/Chicago' FROM foo;
           timezone
-------------------------------
 2023-11-27 22:03:47.654482+00
(1 row)

This is because the value is going the "opposite" direction

this_db=> SELECT pg_typeof(t AT TIME ZONE 'America/Chicago') FROM foo;
          pg_typeof
-----------------------------
 timestamp without time zone
(1 row)

this_db=> SELECT pg_typeof(t::TIMESTAMP AT TIME ZONE 'America/Chicago') FROM foo;
        pg_typeof
--------------------------
 timestamp with time zone
(1 row)

This is compounded even more if the session TimeZone is something other than UTC (because the t::TIMESTAMP "truncation" is highly depending on the session TimeZone)

this_db=> SET TimeZone = 'America/Chicago';
SET
this_db=> SELECT t AT TIME ZONE 'America/Chicago' FROM foo;
          timezone          
----------------------------
 2023-11-27 10:03:47.654482
(1 row)

this_db=> SELECT t::TIMESTAMP AT TIME ZONE 'America/Chicago' FROM foo;
           timezone            
-------------------------------
 2023-11-27 10:03:47.654482-06
(1 row)

Luckily 2023-11-27 10:03:47.654482 is the value we want, but if we were introduce a 3rd timezone into the mix, more problems

this_db=> SET TimeZone = 'America/Denver';
SET

this_db=> SELECT t AT TIME ZONE 'America/Chicago' FROM foo;
          timezone          
----------------------------
 2023-11-27 10:03:47.654482
(1 row)

this_db=> SELECT t::TIMESTAMP AT TIME ZONE 'America/Chicago' FROM foo;
           timezone            
-------------------------------
 2023-11-27 08:03:47.654482-07
(1 row)

Teardown

thid_db=> DROP TABLE foo;
DROP TABLE