首页 文章

Postgresql：如何从时间戳，时区字段正确创建带时区的时间戳

提问于
浏览 次
3

我有一个没有时区的时间戳表 . YYYY-MM-DD HH：MM：SS

和一个字段“时区”，太平洋为“P”，山为“M” .

我需要创建一个“带时区的时间戳”字段

鉴于我有两个字段，有没有办法正确地解释夏令时？

具体来说：时间戳：2013-11-03 01:00:00时区：“P”将成为：2013-11-03 01：00：00-07

和时间戳：2013-11-03 03:00:00 timezone：“P”将成为：2013-11-03 03：00：00-08

postgresql timezone timestamp

3 回答

  • 0

    如果你考虑他们的名字， 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 表示的时刻将是不明确的并且取决于客户端设置 .

    据推测，您使用"timezone"列（ P 或 M ）和 TIMESTAMP WITHOUT TIME ZONE 来补偿输入值的模糊性 .

    原则上，如果您与存储时间戳的时区位于相同的相对时区，则应该返回相同的值，因此如果您已将客户端设置为 US/Pacific 时区并且已存储 2013-11-03 03:00:00 你的 P 时区，你应该得到 2013-11-03 03:00:00 . 但是，这仅在相对值没有歧义时才有效 .

    你的第一个例子中的问题是已经存在一些歧义：

    时间戳：2013-11-03 01:00:00 timezone：“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'之间进行更改，具体取决于该时刻的DST设置，这将自动为您完成，但您应该首先使用 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更改 .

    当然值得阅读Date/Time types section of the PostgreSQL manual，并注意AT TIME ZONE documentation中表格的"return types"列，以便更好地理解这些问题 .

    回复于
  • 1

    首先，当说结果将成为例如 2013-11-03 01:00:00-07 时，应该补充说这实际上取决于SQL客户端的时区设置 . 例如，欧洲时间的会话永远不会将 2013-11-03 01:00:00-07 视为 timestamp with time zone 的值，因为没有欧洲国家永远在 GMT-07 .

    也就是说，转换可以使用AT TIME ZONE构造应用于 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);

    结果是：

    timezone        
------------------------
 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 时区中首先发生的小时 Span ，然后是DST切换后 -08 时区中的第二次 . postgres的解释是在 -08 时区看到它 . 如果我们考虑前一分钟，它会落入 -07 时区 .

    回复于
  • 1

    检查这是否对您有意义

    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
    回复于

相关问题