在不同PostgreSQL服务器上提取时期的结果不同 [英] Different results for extract epoch on different PostgreSQL servers

问题描述

我们将时间戳转换为纪元，对它们进行一些数学运算，然后再将其转换回时间戳。数据库中的所有时间均为没有时区的时间戳。

We convert time stamps to epoch, do some math on them and then convert them back to time stamps. All times in the database are TIMESTAMP WITHOUT TIME ZONE.

由于在英国时间此处是夏令时在一台服务器上关闭了一个小时，但在另一台服务器上关闭了一个小时，所以我做了一点测试：

Since the switch to summer time here in the UK times are off by one hour on one server but not on the other so I did a little test:

SHOW SERVER_VERSION;
SHOW TIMEZONE;
SELECT extract(EPOCH FROM TIMESTAMP '1970-01-01');

在一台服务器上，我得到

On one server I get

 server_version 
----------------
9.1.15
(1 row)

 TimeZone 
----------
GB
(1 row)

 date_part 
-----------
         0
(1 row)

但是另一方面

 server_version 
----------------
9.3.6
(1 row)

 TimeZone 
----------
GB
(1 row)

 date_part 
-----------
     -3600
(1 row)

是否有任何服务器设置可能引起此问题？

Are there any server settings which could be causing this?

还是的行为提取在Postgres 9.1之后更改？

Or did the behaviour of extract change after Postgres 9.1?

推荐答案

是的，提取的行为在PostgreSQL 9.2版中进行了更改。从发行说明：

Yes, the behavior of extract changed in PostgreSQL version 9.2. From the release notes:

• 使 EXTRACT（EPOCH FROM时间戳，不带时区） 测量当地午夜的时间，而不是UTC午夜（Tom Lane）。

• Make EXTRACT(EPOCH FROM timestamp without time zone) measure the epoch from local midnight, not UTC midnight (Tom Lane)

此更改将还原版本7.3中经过深思熟虑的更改。从UTC午夜开始进行测量是不一致的，因为它使结果取决于 时区 设置，对于不带时区的时间戳的计算不应该这样。通过将输入值强制转换为带时区的 timestamp 。

This change reverts an ill-considered change made in release 7.3. Measuring from UTC midnight was inconsistent because it made the result dependent on the timezone setting, which computations for timestamp without time zone should not be. The previous behavior remains available by casting the input value to timestamp with time zone.

这可能是造成差异的原因，因为根据文档，

This might be what is causing the difference, because according to the docs,

SQL标准要求只写 timestamp 等效于不带时区的时间戳，而PostgreSQL尊重这种行为。

The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior.

如@unique_id所建议，使用带有时区的时间戳（又名 timestamptz ）删除不一致之处。

As @unique_id suggests, using timestamp with time zone (a.k.a. timestamptz) should remove the inconsistency.

这篇关于在不同PostgreSQL服务器上提取时期的结果不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！