我有一个没有时区的时间戳表。年-月-日呵呵:唰唰
��以及字段"时区",其中"P"表示太平洋或"M"表示山地。
我需要创建一个"时区时间戳"类型的字段
鉴于我有两个字段,有没有办法正确考虑夏令时?
具体说来:时间戳: 2013-11-03 01:00:00时区:"P"将变为: 2013-11-03 01:00:00-07
和时间戳: 2013-11-03 03:00:00时区:"P"将变为: 2013-11-03 03:00:00-08
TIMESTAMP WITHOUT TIME ZONE
和TIMESTAMP WITH TIME ZONE
(TIMESTAMPTZ
(之间的区别可能很难理解,如果你考虑它们的名字。(事实上,这些规范似乎足够混乱,以至于各种RDBMS以不同的方式实现它。
在 PostgreSQL 中,这两种类型都不存储存储值时的时区,而是TIMESTAMPTZ
根据 UTC 引用将值存储为精确的时刻,而TIMESTAMP WITHOUT TIME ZONE
始终是相对的。
- 查询时,将调整
TIMESTAMPTZ
,以表示最初存储的同一时刻(在世界的任何部分(,以及客户端配置的当前时区中的时刻。 - 相对于客户端配置的时区,
TIMESTAMP WITHOUT TIME ZONE
的值始终相同,即使您从中查询它的时区不同也是如此:2013-11-03 03:00:00
表示的时刻将不明确,并且取决于客户端设置。
据推测,您在TIMESTAMP WITHOUT TIME ZONE
中使用了"时区"列(P
或M
(来补偿输入值的歧义。
原则上,如果您与存储时间戳的时区位于同一相对时区,则应返回相同的值,因此,如果您已将客户端设置为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
和"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);
结果:
| 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
文档中表的"返回类型"列,以便更好地理解这些问题。
首先,当说结果会变成例如2013-11-03 01:00:00-07
时,应该补充一点,这实际上取决于SQL客户端的时区设置。例如,欧洲时间的会话永远不会将2013-11-03 01:00:00-07
读取为timestamp with time zone
的值,因为没有欧洲国家处于GMT-07
。
也就是说,转换可以通过应用于timestamp without time zone
的时区构造来完成。
假设我们从US/Pacific
时区运行它:
SET time zone 'US/Pacific';
SELECT t AT TIME ZONE
case z when 'P' then 'US/Pacific' when 'M' then 'US/Mountain' end
from (values
('2013-11-03 01:00:00'::timestamp, 'P'),
('2013-11-03 03:00:00'::timestamp, 'P')
) as v(t,z);
结果是:
时区 ------------------------ 2013-11-03 01:00:00-08 2013-11-03 03:00:00-08
2013-11-03 01:00:00 AT time zone 'US/Pacific'
具有歧义,因为它属于小时跨度,该小时跨度首先发生在-07
时区中,然后在 DST 切换后的-08
时区中第二次发生。对 postgres 的解释是在-08
时区中看到它。如果我们考虑前一分钟,它属于-07
时区。
检查这对你是否有任何意义
set timezone to 'PST8PDT';
select now();
now
-------------------------------
2013-09-28 03:24:20.169189-07
select ts,
ts at time zone 'PST' as "PST",
ts at time zone 'PDT' as "PDT"
from (values
('2013-11-03 01:00:00'::timestamp),
('2013-11-03 02:00:00'),
('2013-11-03 03:00:00')
) s (ts)
;
ts | PST | PDT
---------------------+------------------------+------------------------
2013-11-03 01:00:00 | 2013-11-03 01:00:00-08 | 2013-11-03 01:00:00-07
2013-11-03 02:00:00 | 2013-11-03 02:00:00-08 | 2013-11-03 01:00:00-08
2013-11-03 03:00:00 | 2013-11-03 03:00:00-08 | 2013-11-03 02:00:00-08