Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tztest (col1 timestamp);

SET timezone = 'UTC';  -- has no effect on "timestamp" values!
INSERT INTO tztest (col1) VALUES ('2023-02-01 10:10:10');
SELECT col1 AS t1, extract(epoch FROM col1) FROM tztest;

CREATE TABLE

SET

INSERT 0 1

t1
extract

2023-02-01 10:10:10
1675246210.000000

SELECT 1

SET timezone = 'America/New_York';  -- would affect conversion without explicit time zone
ALTER TABLE tztest ALTER COLUMN col1 TYPE timestamptz
USING col1 AT TIME ZONE 'UTC';      -- but no effect with explicit time zone
SELECT col1 AS t2, extract(epoch FROM col1) FROM tztest;  -- still affects *display*

SET

ALTER TABLE

t2
extract

2023-02-01 05:10:10-05
1675246210.000000

SELECT 1

SET timezone = 'UTC';  -- affects *display*
SELECT col1 AS t3, extract(epoch FROM col1) FROM tztest;

SET

t3
extract

2023-02-01 10:10:10+00
1675246210.000000

SELECT 1