PostgreSQL 更改类型时间戳，不带时区 -> 带时区

Question

问题很简单：如果我已经在不带时区的列类型timestamp中有数据，如果我将类型设置为带时区的timestamp，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的手册：

如果省略了[USING子句]，则默认转换与从旧数据类型转换为新数据类型的赋值相同。

根据日期/时间类型的手册

不带时区的时间戳和带时区的时间戳记之间的转换通常假定不带时区的timestamp值应取为或给定为时区local time。可以使用AT TIME ZONE为转换指定不同的时区。

Answer 2

最好显式指定时区。例如，如果没有时区的时间戳应该包含UTC时区中的时间戳，那么您应该注意客户端或服务器的时区可能会扰乱这里的一切。

最好的方法是明确指定要使用的时区：

ALTER TABLE a_table
    ALTER COLUMN ts_column 
    TYPE TIMESTAMP WITH TIME ZONE
        USING ts_column AT TIME ZONE 'UTC'

（和往常一样，您可能希望在事务内部运行DDL语句，这样您仍然可以通过一次回滚来恢复更改）