Postgresql：如何从时间戳、时区字段正确创建带时区的时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19062094/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql: how to correctly create timestamp with timezone from timestamp, timezone fields
提问by skihansen
I have a table with a timestamp without time zone. YYYY-MM-DD HH:MM:SS
我有一个没有时区的时间戳表。YYYY-MM-DD HH:MM:SS
and a field "timezone" that is either "P" for Pacific or "M" for Mountain.
和一个字段“时区”，要么是太平洋的“P”，要么是山的“M”。
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?
鉴于我拥有的两个字段，有没有办法正确地考虑夏令时？
Specifically: timestamp: 2013-11-03 01:00:00 timezone: "P" would become: 2013-11-03 01:00:00-07
具体：时间戳：2013-11-03 01:00:00 时区：“P”将变为：2013-11-03 01:00:00-07
and timestamp: 2013-11-03 03:00:00 timezone: "P" would become: 2013-11-03 03:00:00-08
和时间戳：2013-11-03 03:00:00 时区：“P”将变为：2013-11-03 03:00:00-08
回答by Daniel Vérité
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-07as the value of a timestamp with time zonebecause no european country is ever at GMT-07.
首先，当说结果会变成例如时2013-11-03 01:00:00-07，应该补充一点，这实际上取决于SQL客户端的时区设置。例如，欧洲时间的会话永远不会读取2013-11-03 01:00:00-07为 a 的值，timestamp with time zone因为没有欧洲国家永远处于GMT-07.
That said, the conversion can be done with the AT TIME ZONEconstruct applied to a timestamp without time zone.
也就是说，可以通过将AT TIME ZONE构造应用于timestamp without time zone.
Assuming we run this from the US/Pacifictime 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);
The result is:
结果是：
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'has an ambiguity because it belongs to the hour span which happens first in the -07timezone, and then a second time in the -08timezone after the DST switch. The interpretation of postgres is to see it in the -08timezone. If we consider the minute before, it falls into the -07timezone.
2013-11-03 01:00:00 AT time zone 'US/Pacific'有歧义，因为它属于首先在-07时区中发生的小时跨度，然后-08在夏令时切换后在时区中发生第二次。postgres 的解释是在-08时区看。如果我们考虑前一分钟，则它属于-07时区。
回答by Bruno
The difference between TIMESTAMP WITHOUT TIME ZONEand TIMESTAMP WITH TIME ZONE(TIMESTAMPTZ) can be quite tricky to understand if you consider their names. (In fact, the specifications seem to be sufficiently confusing so that various RDBMS implement it in a different way.)
如果考虑它们的名称，就很难理解TIMESTAMP WITHOUT TIME ZONE和TIMESTAMP WITH TIME ZONE( TIMESTAMPTZ)之间的区别。（事实上，规范似乎已经足够混乱，以至于各种 RDBMS 以不同的方式实现它。）
In PostgreSQL, neither types store the time zone of the value when it was stored, but TIMESTAMPTZstores the value as a precise instant in time based on the UTC reference, whereas TIMESTAMP WITHOUT TIME ZONEis always relative.
在 PostgreSQL 中，两种类型都不存储值在存储时的时区，而是TIMESTAMPTZ将值存储为基于 UTC 引用的精确时刻，而TIMESTAMP WITHOUT TIME ZONE始终是相对的。
- When queried, a
TIMESTAMPTZwill be adjusted to represent the same instant in time as initially stored (in whichever part of the world this was) as the instant it would be in the current time zone as configured by the client. - A
TIMESTAMP WITHOUT TIME ZONEwill always be the same value relative to the time zone configured by the client, even if the time zone where you query it from differs: the instant represented by2013-11-03 03:00:00will be ambiguous and depend on the client settings.
- 查询时，
TIMESTAMPTZ将调整 a 以表示与最初存储的时间相同的时刻（无论是在世界的哪个部分），因为它在客户端配置的当前时区中的时刻。 - A
TIMESTAMP WITHOUT TIME ZONE将始终是相对于客户端配置的时区的相同值，即使您查询它的时区不同：表示的时刻2013-11-03 03:00:00将是不明确的并且取决于客户端设置。
Presumably, you used your "timezone" column (Por M) with your TIMESTAMP WITHOUT TIME ZONEto compensate for the ambiguity in the inputvalue.
据推测，您使用“时区”列（P或M）TIMESTAMP WITHOUT TIME ZONE来补偿输入值中的歧义。
In principle, if you are in the same relative time zone as the one where you stored the timestamp, you should get the same value back, so if you've set your client in the US/Pacifictime zone and if you've stored 2013-11-03 03:00:00in your Ptime zone, you should get 2013-11-03 03:00:00back. However, this is only valid when there is no ambiguity in the relative value.
原则上，如果您与存储时间戳的相对时区处于相同的相对时区，则您应该获得相同的值，因此如果您已将客户端设置在US/Pacific时区中并且已存储2013-11-03 03:00:00在您的P时间区，你该2013-11-03 03:00:00回去了。但是，这仅在相对值没有歧义时才有效。
The problem here in your first example is that there already is some ambiguity:
您的第一个示例中的问题是已经存在一些歧义：
timestamp: 2013-11-03 01:00:00 timezone: "P" would become: 2013-11-03 01:00:00-07
时间戳：2013-11-03 01:00:00 时区：“P”将变为：2013-11-03 01:00:00-07
2013-11-03 01:00:00can represent two distinct instants in time in the US/Pacifictime zone, so with just 2013-11-03 01:00:00and "P", you've already lost information that you won't be able to recover.
2013-11-03 01:00:00可以代表US/Pacific时区中两个不同的时间瞬间，因此仅使用2013-11-03 01:00:00和"P"，您已经丢失了无法恢复的信息。
If you just wanted it to change between '-08' and '-07' depending on the DST setting at that instant in time, this would have been done for you automatically, but you should have use a TIMESTAMPTZin the first place, to be precise on which instant in time you were representing.
如果您只是希望它根据当时的 DST 设置在“-08”和“-07”之间进行更改，那么这将自动为您完成，但您应该TIMESTAMPTZ首先使用 a ，以便精确到您所代表的时间。
Here is an example where the initial time zone is kept, so you can see the change between '-08' and '-07':
这是保留初始时区的示例，因此您可以看到“-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 |
Unfortunately, there is no way to handle DST change with just your two fields.
不幸的是，仅凭您的两个字段无法处理 DST 更改。
It's certainly worth reading the Date/Time types section of the PostgreSQL manual, as well as paying attention to the "return types" column of the table in the AT TIME ZONEdocumentationfor a better understanding of these problems.
阅读PostgreSQL手册的日期/时间类型部分当然值得，同时注意AT TIME ZONE文档中表格的“返回类型”列，以便更好地理解这些问题。
回答by Clodoaldo Neto
Check if this makes any sense to you
检查这对您是否有意义
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
