PostgreSQL更改类型时间戳（无时区）->（带时区）

Question

问题很简单：如果我已经有了一个列类型为不带时区的时间戳（timestamp without time zone），如果我将其类型设置为带时区的时间戳（timestamp with time zone），PostgreSQL 会对这些数据做什么？

- gyorgyabraham

---

你说得没错，但在我启动我的应用程序/数据库/任何东西之前，答案可能会出现。 - gyorgyabraham

“因为它曾经有效”是了解每次结果的非常糟糕的方式！ - Philip Couling

@couling：那么您的意思是，如果运行相同的语句多次，即使所有输入参数都相同，也会产生不同的结果？在这种情况下，dbhenur的答案将无法证明任何事情，因为根据您的说法，仅运行该语句一次不能保证它下一次能以相同的方式工作。 - user330315

我不是在暗示，而是在陈述。看看给出的答案！你会话所在的时区会影响结果。我见过这种情况让人们措手不及，在SQL窗口（命令行上）执行查询得到一个结果，在应用程序中使用它又得到了另一个结果（由于他们的配置文件更改了时区，而他们并不知道）。 - Philip Couling

进一步来说，考虑一个没有ORDER BY子句的SQL查询。返回的行的顺序是官方未定义的；RDBMS将只是以最容易的方式返回它们。我见过开发人员错误地认为他们可以依赖于最旧的行（最长时间自更新）首先出现。他们之所以这样认为，仅仅是因为他们“尝试了一下”。...debenhur的答案是正确的，尽管他的理由不够充分。更好的方法是参考手册。 - Philip Couling

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

根据修改表的手册：

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

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

没有时区的时间戳和带有时区的时间戳之间的转换通常假定没有时区的时间戳值应该被视为或给出为时区本地时间。可以使用AT TIME ZONE指定不同的时区进行转换。

- dbenhur

---

是的。这个行为与将没有时区的时间戳转换为带有时区的时间戳的强制转换完全相同。如果您想要不同的内容，可以使用 ALTER COLUMN column TYPE type USING <expression> 并使用时间函数来处理时区。http://www.postgresql.org/docs/current/static/functions-datetime.html - Philip Couling

Answer 2

最好明确指定时区。如果您的时间戳没有时区信息，但是假定它们包含UTC时区的时间戳，则应注意客户端或服务器的时区可能会对此造成影响。

最好的方式是显式地指定要使用的时区：

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

（而且一如既往，您可能希望在事务内运行DDL语句，以便您仍然可以通过单个回滚撤消更改）

- Antti Haapala -- Слава Україні