和...之间的不同TIMESTAMP WITHOUT TIME ZONE
and TIMESTAMP WITH TIME ZONE
(TIMESTAMPTZ
)如果你考虑他们的名字,可能会很难理解。 (事实上,这些规范似乎非常混乱,因此各种 RDBMS 以不同的方式实现它。)
在 PostgreSQL 中,这两种类型都存储值存储时的时区,但是TIMESTAMPTZ
根据 UTC 参考将该值存储为精确的瞬时时间,而TIMESTAMP WITHOUT TIME ZONE
总是相对的。
- 当询问时,一个
TIMESTAMPTZ
将被调整为表示最初存储的同一时刻(无论在世界的哪个部分),以及客户端配置的当前时区的时刻。
- A
TIMESTAMP WITHOUT TIME ZONE
相对于客户端配置的时区,它将始终是相同的值,即使您查询的时区不同:2013-11-03 03:00:00
将是不明确的并且取决于客户端设置。
据推测,您使用了“时区”列(P
or M
) 和你的TIMESTAMP WITHOUT TIME ZONE
来弥补其中的歧义input value.
原则上,如果您与存储时间戳的相对时区处于相同的相对时区,则应该得到相同的值,因此如果您已将客户端设置为US/Pacific
时区以及您是否已存储2013-11-03 03:00:00
在你的P
时区,你应该得到2013-11-03 03:00:00
后退。然而,这仅在相对值不存在歧义的情况下才有效。
第一个示例中的问题是已经存在一些歧义:
时间戳:2013-11-03 01:00:00 时区:“P”将变为:2013-11-03
01:00:00-07
2013-11-03 01:00:00
可以代表两个不同的时刻US/Pacific
时区,所以只需2013-11-03 01:00:00
and "P"
,您已经丢失了无法恢复的信息。
如果您只是希望它根据当时的 DST 设置在“-08”和“-07”之间更改,那么这会自动为您完成,但您应该使用TIMESTAMPTZ
首先,准确地说是你所代表的时间点。
这是保留初始时区的示例,因此您可以看到“-08”和“-07”之间的变化:
SET time zone 'US/Pacific';
SELECT t AS "Date/Time for US/Pacific",
t AT time zone 'UTC' "Date/Time in UTC"
FROM (VALUES
('2013-11-03 00:00:00-07'::timestamptz),
('2013-11-03 01:00:00-07'::timestamptz),
('2013-11-03 02:00:00-07'::timestamptz),
('2013-11-03 03:00:00-07'::timestamptz)) AS v(t);
Results:
| DATE/TIME FOR US/PACIFIC | DATE/TIME IN UTC |
|--------------------------|---------------------|
| 2013-11-03 00:00:00-07 | 2013-11-03 07:00:00 |
| 2013-11-03 01:00:00-07 | 2013-11-03 08:00:00 |
| 2013-11-03 01:00:00-08 | 2013-11-03 09:00:00 |
| 2013-11-03 02:00:00-08 | 2013-11-03 10:00:00 |
不幸的是,仅用两个字段无法处理 DST 更改。
这当然值得一读PostgreSQL 手册的日期/时间类型部分 http://www.postgresql.org/docs/current/static/datatype-datetime.html,以及注意表中的“返回类型”列AT TIME ZONE文档 http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT为了更好地理解这些问题。