PostgreSQL：如何从时间戳，时区字段中正确创建带有时区的时间戳 [英] Postgresql: how to correctly create timestamp with timezone from timestamp, timezone fields

问题描述

我有一个带有时间戳的表，该时间戳没有时区。
YYYY-MM-DD HH：MM：SS

I have a table with a timestamp without time zone. YYYY-MM-DD HH:MM:SS

和一个字段时区，对于太平洋来说是 P，对于山是 M。

and a field "timezone" that is either "P" for Pacific or "M" for Mountain.

我需要创建一个类型为带有时区的时间戳的字段

I need to create a field of type "timestamp with time zone"

鉴于我拥有的两个字段，是否有一种方法可以正确说明夏令时？

Given the two fields I have, is there a way to do this that correctly accounts for Daylight Saving Time?

具体来说：
时间戳：2013-11-03 01:00:00
时区： P
变为：2013-11-03 01：00：00-07

Specifically: timestamp: 2013-11-03 01:00:00 timezone: "P" would become: 2013-11-03 01:00:00-07

和
时间戳：2013 -11-03 03:00:00
时区： P
将变为：2013-11-03 03：00：00-08

and timestamp: 2013-11-03 03:00:00 timezone: "P" would become: 2013-11-03 03:00:00-08

推荐答案

首先，当说结果变成例如 2013-11-03 01：00：00-07 时，它应该添加这实际上取决于SQL客户端的时区设置。例如，在欧洲时间的会话永远不会将时间戳记与时区的值读为 2013-11-03 01：00：00-07 ，因为没有欧洲国家曾经参加过 GMT-07 。

First, when saying that the result would become for example 2013-11-03 01:00:00-07, it should be added that this actually depends on the time zone setting of the SQL client. For instance a session in european time will never read 2013-11-03 01:00:00-07 as the value of a timestamp with time zone because no european country is ever at GMT-07.

可以使用在时区进行转换构造应用于没有时区的时间戳。

That said, the conversion can be done with the AT TIME ZONE construct applied to a timestamp without time zone.

假定我们从美国/太平洋时区：

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时区美国/太平洋 含糊不清，因为它属于在 -07 时区中首先发生的小时跨度，然后在DST切换后的 -08 时区中第二次。 Postgres的解释是在 -08 时区看到它。如果我们考虑前一分钟，它属于 -07 时区。

2013-11-03 01:00:00 AT time zone 'US/Pacific' has an ambiguity because it belongs to the hour span which happens first in the -07 timezone, and then a second time in the -08 timezone after the DST switch. The interpretation of postgres is to see it in the -08 timezone. If we consider the minute before, it falls into the -07 timezone.

这篇关于PostgreSQL：如何从时间戳，时区字段中正确创建带有时区的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！