PostgreSQL alter type timestamp without time zone -> with time zone

Question

问题很简短:如果我已经在没有时区的时间戳类型的列中有数据，如果我将类型设置为带时区的时间戳，postgresql 将如何处理这些数据？

Answer 1

它将当前值保留在本地时间并将时区设置为本地时间的偏移量:

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

根据 Alter Table 的手册:

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

根据 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.