postgresql - Postgres 通过时区转换防止时间戳

Question

我有一张表，用于存储带时区的 iso 日期。我意识到日期应该“始终”存储为 utc，但我对该规则有一个异常(exception)。时间戳与它们运行的​​服务器没有任何关系。我希望能够像这样存储一个 iso 日期:2016-03-06T01:15:52-06:00并且无论服务器的时区或其他任何我想要返回的时间戳:2016-03-06T01:15:52-06:00

目前，如果我插入一个 iso 日期，它会自动将其转换为服务器时区。我的上述日期被转换为:2016-03-06 07:15:52+00(服务器为 utc)

我唯一能想到的是将时区偏移量存储在单独的列中，将我的日期存储为 utc，然后使用偏移量列进行转换，非常困惑。肯定有一种方法可以将我的日期存储在一列中并按照最初创建的方式将其取出？

Answer 1

您提出的解决方案是正确的。或者更准确地说，它是几个正确的实现之一。以下任何一项都有效:

• 将 UTC 时间戳存储在一个字段中，将偏移量存储在另一个字段中。
• 在一个字段中存储本地时间戳，在另一个字段中存储偏移量。
• 在一个字段中存储本地日期，在另一个字段中存储time with time zone。 (虽然 time with time zone 通常不鼓励...)
• 将 UTC 时间戳存储在一个字段中，将本地时间戳存储在另一个字段中。

到目前为止最简单的是第一个，您已经提出了。

我会避免在 text 字段中存储时间戳，因为它们往往无法非常有效地进行搜索。

另请注意 - 如果您有 SQL Server 背景，您可能还记得它的 datetimeoffset 类型，它在字段中存储本地日期时间和偏移量，并在索引期间使用等效的 UTC。人们普遍认为 Postgres 和 MySQL 的 timestamp with time zone 会有相同的行为，但事实并非如此。他们只是使用session 时区与 UTC 相互转换。 SQL Server 没有 session 时区的概念，因此存在差异。

请务必阅读 this part of the Postgres docs .