数据库日期时间设计 - 选择正确的列类型

列类型 - DATE、TIME、TIMESTAMP、TIMESTAMPTZ

数据库的日期时间类型各有用途。DATE 仅存储日期 (2026-05-15),TIME 仅存储时间 (14:30:00),TIMESTAMP 将两者组合 (2026-05-15 14:30:00)。PostgreSQL 的 TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE) 是一种特殊类型,它将输入转换为 UTC 存储,并在读取时转换回会话所在的时区。

最重要的设计问题是每个日期时间是否需要时区语义。事件时间戳 (日志、交易) 代表绝对时刻,应使用 TIMESTAMPTZ。生日和纪念日是与时区无关的日期,DATE 就足够了。营业时间如“9:00-18:00”使用不带时区的 TIME,但相关时区必须在其他地方记录。根据含义来选择,而非凭习惯。

预约系统 - 保留本地时间

对于酒店和餐厅预约,预约时间在场所的本地时间中才有意义。将“东京某餐厅晚上 7 点”存储为 10:00 UTC,在没有夏令时的日本完全没问题。但对于纽约的餐厅,UTC 偏移量随季节变化,以 UTC 存储时,如果夏令时规则的应用方式与预期不同,后续可能会被错误解读。

解决方案是将 TIMESTAMPTZ 与场所的 IANA 时区名称一起存储在单独的列中。显示时,使用保存的时区将 UTC 时刻转换回来,始终生成正确的本地时间。另一种模式是将本地时间存储为 TIMESTAMP (无时区) 并附带时区名称,将两者视为正确解释该值所必需的组合。

未来事件 - 当夏令时规则发生变化时

存储未来事件 (明年的会议、几个月后的定期会议) 有独特的陷阱。如果事件所在国家更改了夏令时政策,以 UTC 存储的时间将不再映射到预期的本地时间。将“2027 年 3 月 15 日纽约时间 10:00”存储为 15:00 UTC,如果美国随后废除夏令时,正确的 UTC 将变为 14:00。

补救措施是将未来事件存储为 (本地时间, 时区名称),在显示或提醒时按需转换为 UTC。随着 IANA 数据库更新,转换会自动遵循新规则。已经发生的过去事件可以安全地以 UTC 存储,因为规则变更无法追溯性地改变已经发生的事实。

审计日志 - 防篡改和顺序保证

审计日志时间戳可能作为事件顺序的法律证据,因此需要最严格的设计。使用由数据库服务器的 NOW() 函数生成的 TIMESTAMPTZ,而非应用服务器的时钟。这可以防止控制自身时钟的客户端进行篡改,否则将构成可信度风险。

在分布式系统中,多个数据库节点的时钟可能略有差异。当需要严格排序时,应辅以单调递增的序列号,或采用混合逻辑时钟 (HLC) 方案。MiFID II 等金融法规要求微秒精度的 UTC 可追溯时间戳,因此 NTP/PTP 同步监控成为运维设计的一部分,而非事后考虑。

遗留架构迁移 - 摆脱时区无感知设计

迁移没有时区意识而构建的系统,首先要确定“这些数据隐含假设的是哪个时区?”在日本构建的系统通常假设 JST;在 AWS 上的系统通常假设 UTC;混合来源很常见。在做任何更改之前,先记录每一列的隐含时区。

安全的步骤是: 添加新的 TIMESTAMPTZ 列,以适当的偏移量转换并复制现有数据,将应用切换为从新列读取,验证,最后删除旧列。批量转换时要特别注意夏令时切换期间的数据,尤其是秋季回拨重叠窗口,同一壁钟时间存在两种解释。