PostgreSQL 在没有时区的情况下更改类型时间戳-> 使用时区

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

80578 次浏览
小开
最佳答案

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.

小开

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

The best way is to just specify the timezone to be used explicitly:

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

(And as always, you may want to run the DDL statements inside a transaction so that you can still revert the change with a single rollback)


提交答案