在编程中,
TIMESTAMP WITHOUT TIME ZONE
和
TIMESTAMP WITH TIME ZONE
(
TIMESTAMPTZ
)之间的区别可能很棘手,如果考虑它们的名称。实际上,规范似乎足够混乱,以至于各种关系型数据库管理系统以不同的方式实现它。
在PostgreSQL中,两种类型都不存储值存储时的时区,但是
TIMESTAMPTZ
将该值存储为基于UTC参考的精确时间点,而
TIMESTAMP WITHOUT TIME ZONE
始终是相对的。
当查询时,
TIMESTAMPTZ
将被调整为表示与最初存储的相同的时间点(无论这是世界上的哪个部分),因为它在客户端配置的当前时区中的时间点。
TIMESTAMP WITHOUT TIME ZONE
将始终相对于客户端配置的时区是相同的值,即使您从中查询它的时区不同:由
2013-11-03 03:00:00
所表示的时间点将是模糊的,并取决于客户端设置。
假设您使用了“时区”列(
P
或
M
)和
TIMESTAMP WITHOUT TIME ZONE
来补偿
输入值中的歧义。原则上,如果您与存储时间戳的相对时区相同,则应该得到相同的值,因此,如果您将客户端设置为
US/Pacific
时区,并且在
P
时区中存储了
2013-11-03 03:00:00
,则应该返回
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
和 "P"
,您已经丢失了无法恢复的信息。
如果您只想在那个特定时间切换为“-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);
结果:
| 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手册中的 日期/时间类型部分以及注意 AT TIME ZONE
文档表格中的“返回类型”列,可以更好地理解这些问题。