Re: Alter timestamp without timezone to with timezone rewrites rows - Mailing list pgsql-hackers

On Wed, Jan 13, 2021 at 4:24 PM Dorian Hoxha <dorian.hoxha@gmail.com> wrote:
>
> Hi team,
>
> I have a table with a column of "timestamp without timezone" and I want to alter it to a column of "timestamp with
timezone"without rewriting the rows.
 
>
> Looking at the docs, and doing some quick tests, the data-on-disk is identical for both columns. But when doing an
altertable, looks like it's rewriting the rows: (PostgreSQL 13.1)
 
>
> guru=# create table tt(m timestamp without time zone default now());
> guru=# insert into tt(m) values(now());
> guru=# SELECT xmin, xmax, cmin, cmax, m from tt;
>  xmin | xmax | cmin | cmax |             m
> ------+------+------+------+----------------------------
>   695 |    0 |    0 |    0 | 2021-01-13 11:47:59.146952
> (1 row)
>
> guru=# alter table tt alter column m type timestamp with time zone;
> guru=# SELECT xmin, xmax, cmin, cmax, m from tt;
>  xmin | xmax | cmin | cmax |               m
> ------+------+------+------+-------------------------------
>   696 |    0 |    4 |    4 | 2021-01-13 11:47:59.146952+01
> (1 row)

+01 indicates that there's timezone information added to the data, so
the rows aren't identical. Here's some more SQL run on my laptop which
shows that
postgres=# create table tt(m timestamp without time zone default now());
postgres=# insert into tt(m) values(now());
INSERT 0 1
postgres=# SELECT xmin, xmax, cmin, cmax, m from tt;
 xmin | xmax | cmin | cmax |             m
------+------+------+------+----------------------------
  509 |    0 |    0 |    0 | 2021-01-13 19:23:25.647806
(1 row)

postgres=# show timezone;
   TimeZone
--------------
 Asia/Kolkata
(1 row)

postgres=# set timezone TO 'Asia/Manila';
SET
postgres=# show timezone;
  TimeZone
-------------
 Asia/Manila
(1 row)

postgres=# SELECT xmin, xmax, cmin, cmax, m from tt;
 xmin | xmax | cmin | cmax |             m
------+------+------+------+----------------------------
  509 |    0 |    0 |    0 | 2021-01-13 19:23:25.647806
(1 row)

-- Note that this output is same as the above one even though I have
changed the timezone setting.

postgres=# reset timezone;
RESET
postgres=# show timezone;
   TimeZone
--------------
 Asia/Kolkata
(1 row)

postgres=# alter table tt alter column m type timestamp with time zone;
ALTER TABLE
postgres=# SELECT xmin, xmax, cmin, cmax, m from tt;
 xmin | xmax | cmin | cmax |                m
------+------+------+------+----------------------------------
  510 |    0 |    4 |    4 | 2021-01-13 19:23:25.647806+05:30
(1 row)

postgres=# set timezone TO 'Asia/Manila';
SET
postgres=# SELECT xmin, xmax, cmin, cmax, m from tt;
 xmin | xmax | cmin | cmax |               m
------+------+------+------+-------------------------------
  510 |    0 |    4 |    4 | 2021-01-13 21:53:25.647806+08
(1 row)

See the difference in the output when the timezone setting is changed.

-- 
Best Wishes,
Ashutosh Bapat