时间戳与时区怎么处理
跨时区存取有两条主流路线：MySQL 用 CONVERT_TZ 显式换算，但它依赖时区表是否已导入；PostgreSQL 用 timestamptz 存储（内部就是 UTC），配合 AT TIME ZONE 在展示时换算。工程上通用的建议是统一以 UTC 落库，只在展示层转换。
基本信息
| MySQL 换算 | CONVERT_TZ(dt, from_tz, to_tz) |
|---|---|
| MySQL 前提 | 需要先导入时区表，否则返回 NULL |
| MySQL 类型差异 | TIMESTAMP 随会话时区换算，DATETIME 原样存取 |
| PostgreSQL | timestamptz 内部存 UTC，按 TimeZone 参数显示 |
| PostgreSQL 换算 | ts AT TIME ZONE 'Asia/Shanghai' |
| 存储建议 | 统一 UTC 或统一带时区类型，只在展示层转换 |
MySQL 换算
MySQL 换算
-- 前提：时区表已导入，否则结果静默为 NULL
SELECT CONVERT_TZ('2024-06-01 10:00:00', 'Asia/Shanghai', '+00:00');
-- 结果：2024-06-01 02:00:00
-- 以会话时区为基准做换算
SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00');
-- 验证时区表在不在：返回 NULL 就说明没导入PostgreSQL 换算
PostgreSQL 换算
-- timestamptz 内部按 UTC 存，显示随 TimeZone 变化
SELECT TIMESTAMPTZ '2024-06-01 10:00:00+08' AT TIME ZONE 'UTC';
-- 结果：2024-06-01 02:00:00
SELECT now() AT TIME ZONE 'Asia/Shanghai'; -- 换算成上海墙上时间
SELECT now(); -- 带时区的当前时间两库对照
| 需求 | MySQL 8 | PostgreSQL |
|---|---|---|
| 显式换算 | CONVERT_TZ(dt, from, to) | ts AT TIME ZONE 'zone' |
| 存储类型 | TIMESTAMP 带时区语义，DATETIME 不带 | timestamptz 带时区，timestamp 不带 |
| 换算返回类型 | DATETIME 形态 | timestamp without time zone |
| 常见坑 | 时区表未导入则静默返回 NULL | 混用两种类型比较会隐式转换 |
| 推荐做法 | 统一存 UTC 并统一会话时区 | 统一使用 timestamptz |
典型使用场景
- 跨国业务的时间展示：库内统一 UTC，按用户时区展示
- 日报按北京时间切分：先换算再按天分组
- 排查时间显示不一致：先确认列的时区语义
- 跨库迁移：把日期时间数据统一按 UTC 口径落库
- 证书与令牌有效期：换算到目标时区做校验
- 对账：确认两侧系统的时间基准是否一致
- 导出报表：按使用者所在时区输出墙上时间，避免客服与客户各说一套
MySQL 的 TIMESTAMP 与 DATETIME 行为不同：前者写入时按会话时区换算、读出时再还原；后者原样存取。同一张表混用两种类型，对账时很容易差出几个小时的偏移。
换算方向要与存储口径一致：库里存的是 UTC，展示北京时间要加 8 小时；反过来把北京时间写库，必须先减 8 小时转成 UTC，方向写反会造成固定的时差错误。
MySQL 未导入时区表时 CONVERT_TZ 静默返回 NULL，上游可能把 NULL 当成“时间缺失”继续处理。上线前用一条已知数据验证，返回非 NULL 再发布。