Postgres数据库中的特殊时区处理 [英] Peculiar time zone handling in a Postgres database

问题描述

我在法国的巴黎（ UTC +1 或 CET ）。

现在是 12am （ 00:00 ），我们将在 2016年11月25日。

我的 Postgres 数据库托管在 eu-west-1 区域的 Amazon Web Services （AWS RDS）上。

I'm in Paris, France (UTC+1 or CET).
It's 12am (00:00), we're on the 25th of November 2016.
My Postgres database is hosted on Amazon Web Services (AWS RDS) in the eu-west-1 region.

查询 current_date （或 current_time ）似乎提供的结果与...我的信念不一致。

Querying for the current_date (or current_time) with a specific time zone set seems to deliver results that aren't consistent with... my beliefs.

特别是，查询<使用 CET 时区或 UTC + 1 current_date 产生不同的结果c $ c>一个。

In particular, querying for the current_date yields a different result when using the CET time zone or the UTC+1 one.

SET TIME ZONE 'UTC+01';
select current_date, current_time;

+------------+--------------------+
| date       | timetz             |
+------------+--------------------+
| 2016-11-24 | 22:00:01.581552-01 |
+---------------------------------+

不是，那是昨天-两个小时前。

Nope, that was yesterday -- two hours ago.

SET TIME ZONE 'CET';
select current_date, current_time;

或

SET TIME ZONE 'Europe/Paris';
select current_date, current_time;

+------------+--------------------+
| date       | timetz             |
+------------+--------------------+
| 2016-11-25 | 00:00:01.581552-01 |
+---------------------------------+

有正确的时间和日期。

什么

对我来说太晚了，我将 UTC + 1 和 UTC-1 还是我要忽略的东西更大？
AWS RDS对此有作用吗？

What's going on there?
Is it too late for me and I mixed up UTC+1 and UTC-1 or is there something bigger that I'm overlooking? Does AWS RDS have a role in this?

推荐答案

问题似乎与Amazon RDS无关：它必须这样做遵循PostgreSQL使用的约定。在这种情况下，您要做后退时区名称。您的意思是 'UTC-01' ，其中您写 'UTC + 01' 。

来自手册：

The issue seems unrelated to Amazon RDS: it has to do with the convention used by PostgreSQL. In this case, you do have the time zone name backwards. You mean 'UTC-01' where you write 'UTC+01'.
From the manual:

要记住的另一个问题是，在POSIX时区名称中，
为正偏移量用于格林威治的西位置。在其他
处，PostgreSQL遵循ISO-8601约定，正
时区偏移量是格林威治的东。

因此用于设置时区的时区字符串（以及显示 SHOW时区， ）或 AT TIME ZONE 构造使用 timestamp 中显示的内容的相反符号 （带有时区）的文字！遗憾的是，一方面是ISO和SQL标准，另一方面是POSIX。 （我认为是POSIX的罪魁祸首。）请参阅：

So time zone string used for SET TIME ZONE (and the display of SHOW timezone, accordingly) or the AT TIME ZONE construct use the opposite sign of what's displayed in timestamp (with time zone) literals! That's a very unfortunate disagreement between ISO and SQL standard on the one hand and POSIX on the other. (I think POSIX is to blame.) See:

• 具有AT时区和UTC偏移的奇数

为什么PostgreSQL为什么将UTC偏移量解释为POSIX而不是ISO-8601？

但是 'CET'或'UTC-01'仍然都可能出错巴黎，因为他们没有考虑到夏令时的规则。

_{（DST是人类历史上最谐的概念之一。）}

But 'CET' or 'UTC-01' are both still potentially wrong for Paris because they are not taking rules for daylight saving time into account.
_{(DST is one of the most moronic concepts in the history of mankind.)}

巴黎（与欧洲大部分地区一样）在冬季使用CET，在夏季使用CEST。您使用 CET 进行的测试恰好在11月生效。如果在夏季尝试相同的操作，则会得到错误的结果。

Paris (like most of Europe) uses CET during winter and CEST during summer. Your tests with 'CET' just happen to work in November. If you try the same in the summer time, you get the wrong result.

为了安全起见，请始终使用时区名称 欧洲/巴黎 ，其中考虑了DST规则。调用会更加昂贵。

To be on the safe side, always use the time zone name 'Europe/Paris', which considers DST rules. The call is more expensive.

如果您的时区设置暗示了DST规则，则函数 current_time 会将DST规则考虑在内。但是 UTC-01 是普通时间偏移。我从不使用数据类型带时区的时间或 current_time 开始。再次使用手册：

The function current_time takes DST rules into account if your time zone setting implies any. But 'UTC-01' is a plain time offset. I never use the data type time with time zone or current_time to begin with. The manual once more:

我们不建议使用类型 time with time区域（尽管PostgreSQL支持旧应用程序和
与SQL标准的一致性）

We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard)

考虑：

SELECT '2016-06-06 00:00+0'::timestamptz AT TIME ZONE 'UTC+01' AS plus_wrong
     , '2016-06-06 00:00+0'::timestamptz AT TIME ZONE 'UTC-01' AS minus_right

     plus_wrong      |     minus_right     
---------------------+---------------------
 2016-06-05 23:00:00 | 2016-06-06 01:00:00

SELECT '2016-01-01 00:00+0'::timestamptz AT TIME ZONE 'CET'    AS cet_winter
     , '2016-06-06 00:00+0'::timestamptz AT TIME ZONE 'CEST'   AS cest_summer
     , '2016-06-06 00:00+0'::timestamptz AT TIME ZONE 'CET'    AS cet_no_dst  -- CET wrong!

     cet_winter      |     cest_summer     |     cet_no_dst      
---------------------+---------------------+---------------------
 2016-01-01 01:00:00 | 2016-06-06 02:00:00 | 2016-06-06 01:00:00  -- wrong

SELECT '2016-06-06 00:00+0'::timestamptz AT TIME ZONE 'Europe/Paris' AS paris_summer
     , '2016-01-01 00:00+0'::timestamptz AT TIME ZONE 'Europe/Paris' AS paris_winter

 paris_summer         | paris_winter
----------------------+----------------------
 2016-06-06 02:00:00  | 2016-01-01 01:00:00  -- always right

相关：

• 完全忽略Rails和PostgreSQL中的时区

stackoverflow.com/questions/12042506/time-zone-names-with-identical-properties-yield-different-result-when-applied-to>具有相同属性的时区名称应用于时间戳时会产生不同的结果

数据类型为带时区的时间戳的时区存储

这篇关于Postgres数据库中的特殊时区处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！