Sample Header Ad - 728x90

ALTER timestamp column to timestamptz, without "converting" data?

6 votes
1 answer
4109 views
It seems that altering a column from timestamp without time zone to timestamp with time zone converts the existing data based on the current session time zone at the time of the alter statement. See this example, output shown after each statement
create table tztest (col1 timestamp without time zone);

set timezone = 'UTC';
insert into tztest (col1) values ('2023-02-01 10:10:10');
select col1 as t1, extract(epoch FROM col1) from tztest;
-- → 2023-02-01 10:10:10	1675246210

set timezone = 'America/New_York';
alter table tztest alter column col1 type timestamp with time zone;
select col1 as t2, extract(epoch FROM col1) from tztest;
-- → 2023-02-01 10:10:10-05	1675264210

set timezone = 'UTC';
select col1 as t3, extract(epoch FROM col1) from tztest;
-- → 2023-02-01 15:10:10+00	1675264210
The epoch value changes after the alter command. I was expecting that PG would assume the timestamp value was UTC and not adjust it when changing the type to timestamp with time zone (giving me t2 of 05:10:10 and t3 of 10:10:10). However, it seems PG assumes the session time zone at the time of the alter, and converts them to UTC. Is my understanding correct, and is it expected behavior? On a large table then the alter will update every row, which is something we are concerned about and certainly want to understand.
Asked by Padraic Renaghan (73 rep)
Feb 1, 2023, 11:34 PM
Last activity: May 4, 2025, 08:50 PM