The type of the Postgres expression current_timestamp at time zone 'UTC' is TIMESTAMP WITHOUT TIME ZONE. Consequently, if we attempt to store this in a column of type TIMESTAMP WITH TIME ZONE then Postgres will cast this TIMESTAMP WITHOUT TIME ZONE-value into a TIMESTAMP WITH TIME ZONEusing the time zone configured for the Postgres instance in question. This results in current_timestamp at time zone 'UTC' being wrong for all Postgres instances running with a non-UTC time zone (ie. most).
Note that Postgres internally stores a value of type TIMESTAMP WITH TIME ZONE as a UTC timestamp. Ie. it does not store the time zone component anywhere -- a timestamp with a time zone component is simply converted to UTC and then stored. So when current_timestamp returns e.g. 2022-08-08 11:59:35.958693+02, Postgres will correctly convert this to the UTC time 2022-08-08 09:59:35.958693 -- whereas storing 2022-08-08 09:59:35.958693 in the same column would be cast to UTC+2 (2022-08-08 09:59:35.958693+02) and thus be two hours too early.
Example
psql (11.11)
Type "help" for help.
postgres=# CREATE TABLE test_table (time TIMESTAMP WITH TIME ZONE);
CREATE TABLE
postgres=# select current_timestamp;
current_timestamp
-------------------------------
2022-08-09 12:22:31.430855+02
(1 row)
postgres=# select current_timestamp at time zone 'UTC';
timezone
---------------------------
2022-08-09 10:22:36.10837
(1 row)
postgres=# INSERT INTO test_table VALUES (current_timestamp at time zone 'UTC');
INSERT 0 1
postgres=# SELECT * FROM test_table;
time
-------------------------------
2022-08-09 10:22:52.618104+02
(1 row)
The type of the Postgres expression
current_timestamp at time zone 'UTC'
isTIMESTAMP WITHOUT TIME ZONE
. Consequently, if we attempt to store this in a column of typeTIMESTAMP WITH TIME ZONE
then Postgres will cast thisTIMESTAMP WITHOUT TIME ZONE
-value into aTIMESTAMP WITH TIME ZONE
using the time zone configured for the Postgres instance in question. This results incurrent_timestamp at time zone 'UTC'
being wrong for all Postgres instances running with a non-UTC time zone (ie. most).Note that Postgres internally stores a value of type
TIMESTAMP WITH TIME ZONE
as a UTC timestamp. Ie. it does not store the time zone component anywhere -- a timestamp with a time zone component is simply converted to UTC and then stored. So whencurrent_timestamp
returns e.g.2022-08-08 11:59:35.958693+02
, Postgres will correctly convert this to the UTC time2022-08-08 09:59:35.958693
-- whereas storing2022-08-08 09:59:35.958693
in the same column would be cast to UTC+2 (2022-08-08 09:59:35.958693+02
) and thus be two hours too early.Example