PostgreSQL更改没有时区的时间戳->带时区 [英] PostgreSQL alter type timestamp without time zone -> with time zone

问题描述

问题很简短：如果我已经有没有时区的timestamp列类型的数据，如果我将数据类型设置为带时区的timestamp，那么postgresql对该数据有何作用？

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?

推荐答案

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

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

根据更改表：

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

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

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

没有时区的时间戳和有时区的时间戳之间的转换通常假定没有时区的时间戳值应采用或指定为 timezone 当地时间。可以使用 AT TIME ZONE 指定不同的时区。

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.

这篇关于PostgreSQL更改没有时区的时间戳->带时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！