postgresql PostgreSQL改变没有时区的类型时间戳->有时区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9772825/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL alter type timestamp without time zone -> with time zone
提问by gyorgyabraham
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?
问题很简单：如果我已经在没有时区的列类型时间戳中有数据，如果我将类型设置为带时区的时间戳，postgresql 会如何处理这些数据？
采纳答案by dbenhur
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:
根据Alter Table的手册：
if [the USING clause is]omitted, the default conversion is the same as an assignment cast from old data type to new.
如果省略[USING 子句]，则默认转换与从旧数据类型转换为新数据类型的赋值相同。
According to the manual for Date/Time types
根据日期/时间类型的手册
Conversions between timestamp without time zoneand timestamp with time zonenormally assume that the timestamp without time zonevalue should be taken or given as timezonelocal time. A different time zone can be specified for the conversion using
AT TIME ZONE.
之间的转换时间戳没有时区和时间戳和时区，通常假设没有时区的时间戳值应取或给出时区的本地时间。可以使用 为转换指定不同的时区
AT TIME ZONE。
回答by Antti Haapala
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:
最好明确指定时区。比如说，如果你的时间戳应该是UTC（但没有时区），你应该警惕客户端或服务器的时区可能会在这里搞砸一切。而是写：
ALTER TABLE a ALTER COLUMN t TYPE TIMESTAMP WITH TIME ZONE USING t AT TIME ZONE 'UTC'
