PostgreSQL alter type timestamp without time zone

Question

The question is short: if I already have data in a column type timestamp without time zone, if I set the type to timestamp with time zone, what does postgresql do with this data?

Answer 1

It keeps the current value in localtime and sets the timezone to your localtime's offset:

create table a(t timestamp without time zone, t2 timestamp with time zone);
insert into a(t) values ('2012-03-01'::timestamp);
update a set t2 = t;
select * from a;
          t          |           t2           
---------------------+------------------------
 2012-03-01 00:00:00 | 2012-03-01 00:00:00-08

alter table a alter column t type timestamp with time zone;
select * from a;
           t            |           t2           
------------------------+------------------------
 2012-03-01 00:00:00-08 | 2012-03-01 00:00:00-08

According to the manual for Alter Table:

if [the USING clause is] omitted, the default conversion is the same as an assignment cast from old data type to new.

According to the manual for Date/Time types

Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using AT TIME ZONE.

Answer 2

It is better to specify the time zone explicitly. Say, if your timestamp are supposed to be in UTC (but without timezone), you should be wary of the fact that the timezone of the client or server might be messing everything here. Instead write:

ALTER TABLE a ALTER COLUMN t TYPE TIMESTAMP WITH TIME ZONE USING t AT TIME ZONE 'UTC'