主页 / dba / 问题 / 29960
Accepted
Zac B
Asked: 2012-12-07 10:36:14 +0800 CST

为什么/如何从“没有时区的时间戳”字段中提取（epoch...）的行为在 PostgreSQL 8.4 和 9.2 之间发生变化？

  • 772

语境：

我们目前在生产中运行 PostgreSQL 8.4，并且正在内部测试 9.2 以备将来升级。关于日期出现了一些问题。除了 PostgreSQL 版本和配置之外，运行 8.4 和 9.2 的服务器在其他方面都是相同的。完全相同的数据存储在两组服务器上；它使用pg_dump和传输pg_restore。

在我们数据库的一部分中，我们将日期存储在一个类型为 的字段中timestamp without time zone。

问题：

从该字段访问日期时，extract(epoch..)请求会根据 Postgres 版本返回非常不同的结果，如下所示：

#On Postgres 8.4. entered_timestamp is a "timestamp without time zone" type column:
SELECT entered_timestamp, extract(epoch from entered_timestamp) AS entered_timestamp from <table row>

 entered_timestamp      | entered_timestamp
 ----------------------------+-------------------
 2012-11-01 06:01:39.699612 |  1351774899.69961

但...

#On Postgres 9.2. All tables, schema, and data are identical, the SELECT statement is identical to the previous one:
SELECT entered_timestamp, extract(epoch from entered_timestamp) AS entered_timestamp from <table row>

 entered_timestamp      | entered_timestamp
 ----------------------------+-------------------
 2012-11-01 06:01:39.699612 |  1351749699.69961

如您所见，返回的时间戳是相同的，但返回的纪元数不同；事实上，他们已经离开了几个小时。两台服务器上的服务器日期和 Linux 时区相同（彼此相差一秒）。Postgres 时区也相同：SELECT current_setting('timezone')在两台服务器上运行也返回相同的数据。在两台服务器上运行SELECT now()会返回几乎相同的值。

问题：

  1. 在 PostgreSQL 8.4 和 PostgreSQL 9.2 之间， extract(epoch...)on 类型字段 的行为有何变化？timestamp without time zone

  2. 为什么会发生这种变化？

  3. 有没有办法在不改变我的模式或改变过去的数据（即基于配置的修复）的情况下防止这种变化？

postgresql timezone

1 个回答

  1. Best Answer

    在我自己的机器上测试这个，我得到 1351749699.69961 作为 PostgreSQL 8.4.10 和 9.2.1 的结果。

    使用 PostgreSQL 8.4.10，结果会根据会话时区而变化。对于 9.2，它没有。

    可能这是此更改的效果：从本地而不是 UTC 午夜测量无时区的时间戳记的纪元。这被称为 9.2 兼容性更改。

    更改说明中建议的解决方法是首先将时间戳转换为 timestamptz：而不仅仅是配置更改。

    就个人而言，我觉得 9.2 的行为更舒服。Unix 纪元被定义为 UTC，因此这实际上意味着普通时间戳值被解释为 UTC：无论如何我都是这样使用它们的。

    • 8

相关问题