问题很简单:如果我已经有了一个列类型为不带时区的时间戳(timestamp without time zone),如果我将其类型设置为带时区的时间戳(timestamp with time zone),PostgreSQL 会对这些数据做什么?
它将当前值保留在本地时间,并将时区设置为您本地时间的偏移量:
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
指定不同的时区进行转换。
ALTER COLUMN column TYPE type USING <expression>
并使用时间函数来处理时区。http://www.postgresql.org/docs/current/static/functions-datetime.html - Philip Couling最好明确指定时区。如果您的时间戳没有时区信息,但是假定它们包含UTC时区的时间戳,则应注意客户端或服务器的时区可能会对此造成影响。
最好的方式是显式地指定要使用的时区:
ALTER TABLE a_table
ALTER COLUMN ts_column
TYPE TIMESTAMP WITH TIME ZONE
USING ts_column AT TIME ZONE 'UTC'
(而且一如既往,您可能希望在事务内运行DDL语句,以便您仍然可以通过单个回滚撤消更改)
时区
会影响结果。我见过这种情况让人们措手不及,在SQL窗口(命令行上)执行查询得到一个结果,在应用程序中使用它又得到了另一个结果(由于他们的配置文件更改了时区,而他们并不知道)。 - Philip CoulingORDER BY
子句的SQL查询。返回的行的顺序是官方未定义的;RDBMS将只是以最容易的方式返回它们。我见过开发人员错误地认为他们可以依赖于最旧的行(最长时间自更新)首先出现。他们之所以这样认为,仅仅是因为他们“尝试了一下”。...debenhur的答案是正确的,尽管他的理由不够充分。更好的方法是参考手册。 - Philip Couling