PostgreSQL：无法正确使用带有夏令时的时区

Question

PostgreSQL：无法正确使用带有夏令时的时区

postgresqldatetimetimezonedsttimestamp-with-timezone

3

我想弄清楚Postgres如何处理夏令时与时间间隔的结合。具体来说，我希望允许我的用户创建带有重复日期的事件，例如每天在当地时间下午4点。

对于我正在做的事情，我需要以用户的当地时间存储第一个日期，然后添加一些天数，而不改变用户的当地时间中的时分秒。我希望使用带有完整时区名称的timestamptz（因此它知道何时应用DST？）与简单的一天时间间隔来完成此任务，但它在我的简单示例中失败了：

德国采用CET（+1:00），并在3月28日早上2点切换到CEST（+2:00）。突尼斯全年使用CET。

因此，我期望在3月27日使用timestamptz并添加1天后，在柏林看到不同的UTC偏移量，在突尼斯没有变化，但它们都平等地更改了偏移量，就像突尼斯正在使用DST一样：

select 
'2021-03-27 16:00:00 Africa/Tunis'::timestamptz as "tunis_before_dst",
'2021-03-27 16:00:00 Africa/Tunis'::timestamptz + INTERVAL '1 day' as "tunis_after_dst",
'2021-03-27 16:00:00 Europe/Berlin'::timestamptz as "berlin_before_dst",
'2021-03-27 16:00:00 Europe/Berlin'::timestamptz + INTERVAL '1 day' as "berlin_after_dst"

导致结果如下：

tunis_before_dst: '2021-03-27 16:00:00+01'
tunis_after_dst: '2021-03-28 16:00:00+02'
berlin_before_dst: '2021-03-27 16:00:00+01'
berlin_after_dst: '2021-03-28 16:00:00+02'

浏览 pg_timezone_names，我可以看到我的 Postgres 实例知道 Africa/Tunis 没有 DST - 所以我想知道它为什么会更改其 UTC 偏移量。

我想很明显时区和DST对我来说很困惑，但我处理它们时是否做错了什么或者 timezonetz 不是我想象中的那样？

- d0n.key

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=5f454066ca30a50d88f8adb94a41f061 - Abelisto

@Abelisto 那个似乎可以工作，但是在夏令时之前，它显示突尼斯时间为15:00+0，在之后则为16:00+1 - 没有真正的变化，但为什么显示方式不同呢？我期望它始终以16:00+1的形式显示，因为我使用它在突尼斯当地时间创建了一个时间戳。 - d0n.key

1

@d0n.key：dbfiddle中的显示受其时区设置的控制。请参见https://dbfiddle.uk/?rdbms=postgres_13&fiddle=c14aa7a31c8c9d676869cd0d4c936fdb - Erwin Brandstetter

附言：您显示的结果具有不同的偏移量。在相同的会话（使用相同的“时区”设置）中，Postgres本身不会这样做。 - Erwin Brandstetter

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

首先发泄一下，夏令时的概念是令人惊叹的无稽之谈。甚至名称也很明显是胡说八道。没有挽救任何白天时间。我不敢相信欧盟仍然没有设法废除它，尽管绝大多数人希望摆脱它已经达成共识一段时间了。

接下来要解决的主要误解是：您认为数据类型timestamp with time zone将存储时区信息。但它并不会。这在以下内容中变得明显：

因此，我预计[...]我将在柏林看到一个不同的UTC偏移量，而突尼斯不会发生任何变化 - 但它们两个都以相等的方式改变了偏移量

您在输出中看到的时区偏移量是由当前会话的timezone设置确定的偏移量。时区用作输入/输出修改器/装饰器。Postgres始终在内部存储UTC时间，并且没有任何时区信息。

类型名称有点令人误解。即使最好的人也被误导：

数据类型“timestamp with time zone”中的时区存储

一旦您掌握了这个概念，其他事情就应该变得明显了。

为了保留本地时间（当天的壁钟时间），请使用数据类型timestamp without time zone（timestamp），甚至可以只使用time（永远不要使用损坏的timetz），并附加存储时区信息-最好是时区名称（像您拥有的'Europe/Berlin'），而不是时区缩写或数字偏移量。 timestamp with time zone（timestamptz）是存储独特时间点的正确选择，与任何时区无关。时区偏移量只是一个输入修改器。以下两个字面值完全产生相同的timestamptz值，因为这两个时区恰好在今年这个时间应用相同的偏移量:

'2021-03-27 16:00:00 Africa/Tunis'::timestamptz
'2021-03-27 16:00:00 Europe/Berlin'::timestamptz

但是这些时间有一个小时的差异，因为德国的偏移量根据当地的夏令时制度而改变:

'2021-03-28 16:00:00 Africa/Tunis'::timestamptz
'2021-03-28 16:00:00 Europe/Berlin'::timestamptz