5

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.

1 Answer 1

3

Yes, your understanding is correct. Don't rely on the fallback (the timezone setting of the current session) without setting it yourself explicitly.
If your timestamps shall be interpreted as UTC timestamps, be explicit about it. It's best to set the session timezone for this particular case and run ALTER TABLE with the simple SET DATA TYPE. Only this case can take the shortcut without table rewrite (see below):

SET timezone = 'UTC';
ALTER TABLE tztest ALTER COLUMN col1 TYPE timestamptz;

You can also apply AT TIME ZONE in a USING clause to SET DATA TYPE:

ALTER TABLE tztest ALTER COLUMN col1 TYPE timestamptz
USING col1 AT TIME ZONE 'UTC';

The epoch value stays the same either way, because Postgres stores timestamptz as UTC timestamps internally. Postgres has to rebuild any index involving col1 in any case.

fiddle

See:

Table rewrite?

On a large table then the alter will update every row ...

Except for noted exceptions, ALTER TABLE rewrites the whole table and all indexes (taking an exclusive lock for the duration!). Changing the type to a binary coercible type (like text --> varchar) can avoid it. The manual:

As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed.

Postgres does not realize this for the expression with AT TIME ZONE. But - like Jeff commented - the simple SET DATA TYPE with the session time zone set to UTC qualifies. The release notes for Postgres 12:

Allow ALTER TABLE ... SET DATA TYPE changing between timestamp and timestamptz to avoid a table rewrite when the session time zone is UTC (Noah Misch)

In the UTC time zone, these two data types are binary compatible.

Older versions were not as smart. Compare:

fiddle -- Postgres 11
fiddle -- Postgres 12

Also note that a rewrite is not an UPDATE, which produces dead tuples and bloats the table, but does not interfere with concurrent reads with its weaker lock. This completely rewrites the whole table in pristine condition, but takes an exclusive lock for the duration.

Related:

8
  • Thanks. Very helpful. We are using AWS DMS to convert from IBM DB2 into PG. The DB2 data is in ET. We can't control the DMS connection time zone (it sets to UTC). So the only manner we have fond to get this to work is to rely on ALTER to convert the data. But at our scale I am not sure the ALTER will ever finish. Any other ideas are welcome. Commented Feb 2, 2023 at 0:25
  • 1
    @PadraicRenaghan: I added an important aside. Commented Feb 2, 2023 at 0:32
  • 3
    But note that doing the ALTER TABLE while your session timezone is set to UTC will not cause a table rewrite (it just does that binary coercion thing), while your formulation will cause one. But if the column is indexed, then that index does need to get rebuilt anyway.
    – jjanes
    Commented Feb 2, 2023 at 2:55
  • 1
    @jjanes: Thanks for adding, I wasn't aware of this juicy exception. Can make all the difference for the OP! I updated accordingly Commented Feb 2, 2023 at 4:05
  • 1
    @PadraicRenaghan: Note the update after Jeffs comment. Commented Feb 2, 2023 at 4:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.