不列颠哥伦比亚省、时区与Postgres

# 不列颠哥伦比亚省、时区与 PostgreSQL | Crunchy Data 博客 来源：https://www.crunchydata.com/blog/british-columbia-and-time-zone-changes 2026年3月8日，不列颠哥伦比亚省将其时钟永久调整为太平洋夏令时。在3月，他们按照*spring forward*（春季调快）将时钟调快一小时至 UTC-7，但不会在11月*fall back*（秋季调回）至 UTC-8。从此以后，America/Vancouver 时区的 UTC 偏移量将永久固定为 UTC-7。 让我们借此机会来讨论日期和时区的存储方式。在最基本的示例中，默认的做法是存储 UTC 值，然后根据 UTC 计算本地时间。然而，使用日历系统的人是以本地时间（即墙上时钟时间）来思考的，他们从不考虑 UTC。在修改时区数据后，根据 UTC 为某个区域计算出的时间将与用户输入的本地值不一致。 **如果你在不列颠哥伦比亚省2026年及以后的预约中使用了基于 UTC 的列来存储时间戳，那么你在11月至次年3月的预约可能会错误一个小时！** 计算方式变化的示意图 请注意，`timestamptz` 列并不存储本地时间。它存储的是 UTC 时间，时区仅在插入和查询时用于与 UTC 进行转换。如果你将一个未来的预约以 `timestamptz` 类型存储在 America/Vancouver 时区中，那么在存储时会使用当时的规则将其转换为 UTC。稍后当你查询该预约时，它会使用当前的规则再转换回本地时间。如果从存储到查询期间规则发生了变化，那么你得到的本地时间就不再是用户原本想要的时间。 如果你没有更新你的 `tzdata` 包，那么 PostgreSQL 将不知道这一变化，并且会继续使用旧规则进行转换。Ubuntu 中的 tzdata 包多久更新一次？令人惊讶的是，每隔几个月就会更新一次。 如果你的列使用 `timestamptz` 类型，并且与不列颠哥伦比亚省的客户打交道，可以使用以下 SQL 查询来检查 `tzdata` 包是否已更新： `` SELECT to_char( '2026-12-01 10:00:00'::timestamp AT TIME ZONE 'America/Vancouver', 'HH24:MI:SS OF' ) AS november_2026_vancouver_offset; `` 如果返回的值是 `17:00:00 +00`，则表示 `tzdata` 已更新。但这并不像听起来那么好，因为你需要翻阅日志才能知道未来的预约是在时区调整之前还是之后创建的。 如果返回的值是 `18:00:00 +00`，那么恭喜你！你的 `tzdata` 尚未更新，并且你的数据没有跨越更新。 ## 时区偏移的示例 (https://www.crunchydata.com/blog/british-columbia-and-time-zone-changes#an-example-of-the-timezone-shift) 今年早些时候，一位用户为2026年11月10日在温哥华预约了上午10点。你将其存储为 `timestamptz`： `` INSERT INTO appointments (patient_id, starts_at) VALUES (42, '2026-11-10T10:00:00-08:00'); -- 存储为: 2026-11-10 18:00:00+00 (UTC) `` 2026年4月，`tzdata` 更新发布，推入了新的时区规则。 2026年11月10日，病人按日历上的时间在本地时间上午10点出现。但是当你查询预约时，系统显示其预约时间是本地时间上午11点： `` SELECT starts_at AT TIME ZONE 'America/Vancouver' AS local_time FROM appointments WHERE patient_id = 42; -- 返回: 2026-11-10 11:00:00 `` 注意，计算出的结果比最初输入的时间晚了一个小时。 ## 能应对时区变化的 schema：双列模式 (https://www.crunchydata.com/blog/british-columbia-and-time-zone-changes#a-schema-that-survives-time-zone-changes-dual-column-pattern) 顾名思义，双列模式将数据存储在两列中（实际上是三列）： - 本地时间戳 - 本地时区 - UTC 时间戳 UTC 时间戳列应是一个计算列。使用时间戳和时区来计算 UTC。计算出的 UTC 值也将被存储和查询，以便后台作业发送通知，并简化约束检查（例如预约冲突）。 当*本地意图*具有权威性时，双列模式是必要的：涉及特定时间和地点的人或交付、法律截止日期、日历事件等。 但不要过度使用。当事件已经过去，或者确切的 UTC 时刻具有权威性（日志条目、金融交易、传感器读数）时，请使用普通的 `timestamptz`。双列模式会增加成本和复杂性，只有当未来本地意图必须被保留时才值得付出。 详细的 schema 如下所示： `` CREATE TABLE appointments ( id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY, local_time timestamp NOT NULL, -- 墙上时钟值 timezone_name text NOT NULL, -- IANA 名称: 'America/Vancouver' starts_at_utc timestamptz NOT NULL -- 通过触发器计算 ... ); `` `local_time` 和 `timezone_name` 共同回答了“用户原本想要什么？”这个问题，通过存储墙上日历/墙上时钟值/墙上时钟位置。这些值只应在用户请求时更改。它们将用于计算 `starts_at_utc`。 `starts_at_utc` 可以是用于索引、查询和约束检查的列。它回答了“这个预约当前对应哪个 UTC 时刻？”拥有一个计算并存储的 UTC 值应该能简化你目前使用 UTC 值的方式。 有几种方法可以计算 `starts_at_utc`，可以通过应用程序或数据库。虽然计算出的 UTC 列是生成列的一个很好的例子，但 PostgreSQL 不允许为生成列使用带时区的时间戳列类型，因为 `timestamptz` 不被归类为*不可变*（因为时区规则会变化）。因此，使用触发器在插入和更新时计算 `starts_at`： `` CREATE OR REPLACE FUNCTION recompute_appointment_utc() RETURNS TRIGGER AS $$ BEGIN NEW.starts_at_utc := NEW.local_time AT TIME ZONE NEW.timezone_name; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ts_recompute_starts_at_utc BEFORE INSERT OR UPDATE ON appointments FOR EACH ROW EXECUTE FUNCTION recompute_appointment_utc(); `` ## 双列模式下的时区变化 (https://www.crunchydata.com/blog/british-columbia-and-time-zone-changes#timezone-changes-with-dual-columns) 如果 `tzdata` 更新改变了某个时区的规则，数据库中推导出的 `starts_at_utc` 值就会变得陈旧，需要重新计算。你可以通过一个简单的 `UPDATE` 语句来重新应用转换逻辑： `` UPDATE appointments SET starts_at_utc = local_time AT TIME ZONE timezone_name WHERE timezone_name = 'America/Vancouver' AND starts_at_utc > now(); `` ## RFC 9557 怎么样？ (https://www.crunchydata.com/blog/british-columbia-and-time-zone-changes#what-about-rfc-9557) 2024 年，RFC 9557 发布，它定义了一种新的时间戳格式，看起来像 `1996-12-19T16:39:57-08:00[America/Los_Angeles]`。2025 年 11 月，pgsql-general 论坛上曾进行过简短讨论。由于该*标准*仍然相当新，并且人们还在观望其采用情况，因此尚未推进使用。 然而，RFC 9557 明确声明它并不打算解决以下问题： > 未来在某个指定时区中以本地时间给出的时间，其中该时区定义的变更（例如决定实施或取消夏令时的政治决策）会影响时间戳所代表的瞬时时间； 因此，对于足够遥远的*现实世界*时间，请坚持使用双列模式。 ## 如果 tzdata 已经更新了怎么办？ (https://www.crunchydata.com/blog/british-columbia-and-time-zone-changes#what-to-do-if-tzdata-has-already-updated) 如果你已经更新了 `tzdata` 包以支持新时区，并且你的列值被赋予了未知的 UTC 偏移，而且你的数据库记录了不列颠哥伦比亚省实体的未来时间，那么你手上就有了一个数据项目。理想情况下，你应该： 1. 查找或估算 `tzdata` 包更新的时间 2. 找出所有可能不正确的记录 3. 使用 `updated_at` 时间戳（在 `tzdata` 更新之后）来识别可能受影响的行 4. 制定通知用户时间偏移调整的计划，并考虑提供选择退出或选择加入的途径 5. 在非生产数据集上对可能受影响的行进行时间偏移迁移测试 6. 运行备份，然后在生产环境上运行时间偏移迁移 7. 为受此变化影响的日历项添加 UI 元素 8. 当现已废弃的11月时区变更临近时，再次提醒用户潜在的时区问题 不列颠哥伦比亚省有580万人口，其时区偏好的改变将广泛影响一些数据集，而对另一些则完全没有影响。不要被时区变化打个措手不及；`tzdata` 包的更新频率相当惊人。