在不同的 PostgreSQL 服务器上提取 epoch 的不同结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29536542/
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
Different results for extract epoch on different PostgreSQL servers
提问by peterwimsey
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.
我们将时间戳转换为纪元，对它们进行一些数学运算，然后将它们转换回时间戳。数据库中的所有时间都是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)
But on the other
但另一方面
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?
是否有任何可能导致此问题的服务器设置？
Or did the behaviour of extractchange after Postgres 9.1?
还是extractPostgres 9.1 之后的行为发生了变化？
回答by Dan Getz
Yes, the behavior of extractchanged in PostgreSQL version 9.2. From the release notes:
是的，extract在 PostgreSQL 9.2 版中改变了行为。从发行说明：
Make
EXTRACT(EPOCH FROM timestamp without time zone)measure the epoch from local midnight, not UTC midnight (Tom Lane)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
timezonesetting, which computations fortimestamp without time zoneshould not be. The previous behavior remains available by casting the input value totimestamp with time zone.
使
EXTRACT(EPOCH FROM timestamp without time zone)测量从当地午夜时代，不是UTC午夜（汤姆巷）此更改恢复了 7.3 版中考虑不周的更改。从 UTC 午夜开始测量是不一致的，因为它使结果取决于
timezone设置，而计算timestamp without time zone不应该如此。通过将输入值转换为 ，先前的行为仍然可用timestamp with time zone。
This might be what is causing the difference, because according to the docs,
这可能是造成差异的原因，因为根据文档，
The SQL standard requires that writing just
timestampbe equivalent totimestamp without time zone, and PostgreSQL honors that behavior.
SQL 标准要求编写只
timestamp等价于timestamp without time zone，并且 PostgreSQL 尊重这种行为。
As @unique_id suggests, using timestamp with time zone(a.k.a. timestamptz) should remove the inconsistency.
正如@unique_id 所暗示的那样，使用timestamp with time zone(aka timestamptz) 应该消除不一致。
