postgresql - 关于在 postgresql 中使用时区的策略

Question

我需要在我的应用程序中使用多个时区。

基本上，我的系统会生成数据，地球上任何地方的客户都可以访问这些数据。

由于我的数据有关联的时间戳，我想到了以下处理时区的策略。

在 Postgres 中:

• 使用 ::timestamptz 类型为我的时间戳创建我的表。不允许使用没有时区的时间戳，因为混合使用 ::timestamp 和 ::timestamptz 在我看来就像一颗定时炸弹。

• 将我的 PG 服务器系统的时区设置为 `UTC。

• 在 postgresql.conf 中设置 timezone='UTC'(如果系统的 TZ 是 UTC，可能不需要，但我有点偏执，而且它我不花一分钱)。

• 在创建我的表时添加以下检查:

  CONSTRAINT timestamp_must_be_utc CHECK (date_part('timezone'::text, "my_timestamp_field") = 0::double precision)

• 以 UTC 格式存储我所有的时间戳

在客户端(python + pytz)

• 在他的个人资料中存储客户的时区，例如 'America/Los Angeles'

• 在查询数据时将时区信息发送到 postgres，这样我就可以获得类似 SELECT xxxx FROM yyyy WHERE my_ts >= '2012-11-27 19:13:00+01'::timestamptz 并让 Postgres 转换为 UTC。或者我可以使用 pytz用于转换，但 Postgres 似乎可以很好地完成这项工作。

• 根据客户的时区转换时间戳，以便我可以正确显示数据 + 时间戳。

总而言之，我计划在所有地方都使用 UTC 来存储和查询数据，并且在显示数据时只进行时区转换。

我对 Postgres 及其处理时区的方式没有太多经验。我知道处理不同时区的日期和时间有多难(一旦你必须处理航类时刻表，你就会了解到使用 UTC 是进行日期和时间微积分的唯一可靠方法)这就是我问的原因这个问题，以便比我更有经验的 postgres 用户可以确认或纠正我的策略。

有趣的链接:

• https://stackoverflow.com/a/9576170/1121179
• https://stackoverflow.com/a/10462428/1121179

Answer 1

根据自己的喜好泡茶/咖啡，留出一个小时左右的时间阅读手册的 details在 timestamp处理。稍微尝试一下，一切都会变得清晰。

如果你使用“带时区的时间戳”(timestamptz)，处理不同的时区是没有问题的。它真的应该被命名为“绝对时间戳”并且在内部是 UTC。时区部分不会被存储，它只是用来获取绝对时间。

因此 - 确保您所有的时间戳都包含更新时的相关时区，然后适本地设置您的客户端时区。无论提供它们时所在的时区如何，它们都将按照客户的时区返回给您。

它确实变得繁琐的地方是处理这样一个事实，即一天并不总是 24 小时(有时一个小时不是 3600 秒)并且 DST 发生在不同的日期，具体取决于国家和历史。但这不是 PostgreSQL，而是真实世界。