时区转换

时区是时间数据处理中错误率最高的环节。PostgreSQL 用两种类型区分「绝对时刻」与「本地墙上时间」：timestamptz 内部只存 UTC 微秒，展示时按会话时区渲染；timestamp 则是没有任何时区信息的裸值。AT TIME ZONE 是两者之间的转换枢纽，它的行为方向相反——对 timestamptz 用是「渲染成本地时间」，对 timestamp 用是「按该时区解释成绝对时刻」。混淆这两个方向，会得到整整差一个时区偏移的错误数据。

前置知识

阅读本节前，建议先了解：TO_CHAR / TO_DATE / TO_TIMESTAMP

基础概念

概念	术语	说明
绝对时刻	Instant / Absolute Time	一个全球唯一的时刻，与任何时区无关
墙上时间	Wall Clock Time	钟表上显示的时间，需要时区才能确定绝对时刻
timestamp	timestamp without time zone	只存年月日时分秒，无时区信息，表示墙上时间
timestamptz	timestamp with time zone	内部存 UTC 微秒，展示时按会话时区渲染
会话时区	Session TimeZone	由 TimeZone 参数决定，控制 timestamptz 的输入输出渲染
UTC 偏移	UTC Offset	某个时刻相对 UTC 的差值，如 +08
夏令时	DST	部分时区在特定日期切换偏移，产生「重复时刻」与「不存在的时刻」
时区缩写	Abbreviation	CST / IST 等，同一缩写可能对应多个不同时区

核心模型：

             输入侧                                   输出侧
  '2026-09-24 15:00'  + 会话时区  →  timestamptz  →  按会话时区渲染成 '2026-09-24 15:00+08'
     （墙上时间）                       （UTC 微秒）         （墙上时间）

  会话时区 TimeZone = 'Asia/Shanghai' 时：
    '2026-09-24 15:00'::timestamptz  →  内部 2026-09-24 07:00 UTC
    该值显示出来                  →  '2026-09-24 15:00+08'

  同一个绝对时刻，换个会话再看：
    SET TimeZone = 'UTC';
    该值显示出来                  →  '2026-09-24 07:00+00'

timestamp 与 timestamptz 的对照：

维度	timestamp	timestamptz
内部存储	年月日时分秒（无时区）	UTC 微秒（绝对时刻）
表示含义	墙上时间（模糊）	绝对时刻（精确）
输入是否依赖会话时区	否	是
输出是否依赖会话时区	否	是
跨时区比较	按字面值比较，通常不对	按绝对时刻比较，正确
是否推荐存业务时间	仅在「本地时间语义」明确时	推荐

AT TIME ZONE 的双向语义（这是全篇最关键的表格）：

表达式	输入类型	返回类型	语义
timestamptz AT TIME ZONE 'zone'	timestamptz	timestamp	把绝对时刻渲染成该时区的墙上时间
timestamp AT TIME ZONE 'zone'	timestamp	timestamptz	把墙上时间解释成该时区下的绝对时刻
time AT TIME ZONE 'zone'	time	timetz	为时间补上该时区偏移
timetz AT TIME ZONE 'zone'	timetz	time	去掉时区，转成该时区的墙上时间

记忆口诀：timestamptz 减时区变 timestamp，timestamp 加时区变 timestamptz。方向由输入类型决定，与函数名无关。

语法与用法

-- 1. AT TIME ZONE 双向转换
timestamptz AT TIME ZONE text  → timestamp
timestamp   AT TIME ZONE text  → timestamptz

-- 2. 函数形式（与 AT TIME ZONE 等价）
timezone(text, timestamptz)    → timestamp
timezone(text, timestamp)      → timestamptz

-- 3. 会话时区管理
SHOW TimeZone;
SET  TimeZone = 'Asia/Shanghai';
SET  TimeZone = 'UTC';
RESET TimeZone;

-- 4. 查询可用时区
SELECT name, abbrev, utc_offset, is_dst FROM pg_timezone_names WHERE name = 'Asia/Shanghai';

最小可运行示例：

-- 准备一个绝对时刻
SELECT TIMESTAMPTZ '2026-09-24 15:00:00+08' AS instant;

-- 方向 1：timestamptz → 其他时区的墙上时间（返回 timestamp）
SELECT
  TIMESTAMPTZ '2026-09-24 15:00:00+08' AT TIME ZONE 'UTC'           AS utc_wall,
  TIMESTAMPTZ '2026-09-24 15:00:00+08' AT TIME ZONE 'Asia/Tokyo'    AS tokyo_wall,
  TIMESTAMPTZ '2026-09-24 15:00:00+08' AT TIME ZONE 'America/New_York' AS ny_wall;
-- 结果：2026-09-24 07:00:00 | 2026-09-24 16:00:00 | 2026-09-24 03:00:00

-- 方向 2：timestamp → 按给定时区解释成绝对时刻（返回 timestamptz）
SELECT
  TIMESTAMP '2026-09-24 15:00:00' AT TIME ZONE 'Asia/Shanghai' AS sh_instant,
  TIMESTAMP '2026-09-24 15:00:00' AT TIME ZONE 'UTC'           AS utc_instant;
-- 结果：2026-09-24 15:00:00+08 | 2026-09-24 23:00:00+08
-- （会话时区为 Asia/Shanghai，因此两者渲染出的偏移都是 +08）

-- 函数写法完全等价
SELECT timezone('UTC', TIMESTAMPTZ '2026-09-24 15:00:00+08');
-- 结果：2026-09-24 07:00:00

详细说明

会话时区控制的是「渲染」，不是「存储」

timestamptz 存的是绝对时刻，会话时区只影响它的输入解析与输出渲染。这意味着同一条数据在不同会话里显示不同，但作为绝对时刻是恒等的：

-- 插入时按会话时区解析
SET TimeZone = 'Asia/Shanghai';
CREATE TEMP TABLE t_ts (id int, ts timestamptz);
INSERT INTO t_ts VALUES (1, '2026-09-24 15:00:00');
-- 内部存入：2026-09-24 07:00:00 UTC

-- 换会话再看
SET TimeZone = 'UTC';
SELECT ts FROM t_ts;
-- 结果：2026-09-24 07:00:00+00     （绝对时刻不变，渲染变了）

SET TimeZone = 'America/New_York';
SELECT ts FROM t_ts;
-- 结果：2026-09-24 03:00:00-04     （同一个绝对时刻）

数据没有任何变化，变的只是「用哪个时区的钟表去看它」。这也是为什么服务端会话时区不应被业务代码隐式依赖：换一次连接池配置，所有时间显示都会漂移。

timestamptz 与 timestamp 的赋值转换依赖会话时区：

SET TimeZone = 'Asia/Shanghai';

SELECT
  (TIMESTAMP '2026-09-24 15:00:00')::timestamptz  AS ts_to_tstz,
  (TIMESTAMPTZ '2026-09-24 15:00:00+08')::timestamp AS tstz_to_ts;

     ts_to_tstz      |     tstz_to_ts
---------------------+---------------------
 2026-09-24 15:00:00+08 | 2026-09-24 15:00:00

两个方向的隐式转换都依赖会话时区。想消除这种依赖，一律显式使用 AT TIME ZONE。

时区名、缩写与偏移的优先级

时区参数可以写成三种形式，优先级与精确度依次递减：

写法	示例	特点
时区全名	'Asia/Shanghai'	推荐，包含完整的夏令时规则
缩写	'CST'	有歧义，可能指中国标准时间或美国中部时间
数字偏移	'+08' / '+08:00'	精确但不含夏令时规则，等价于 POSIX 固定偏移

-- 三种写法
SELECT
  TIMESTAMPTZ '2026-09-24 15:00:00+08' AT TIME ZONE 'Asia/Shanghai' AS by_name,
  TIMESTAMPTZ '2026-09-24 15:00:00+08' AT TIME ZONE '+08'           AS by_offset;

-- 无夏令时的时区，两者一致
SELECT
  TIMESTAMPTZ '2026-09-24 15:00:00+08' AT TIME ZONE 'Asia/Shanghai' AS shanghai,
  TIMESTAMPTZ '2026-09-24 15:00:00+08' AT TIME ZONE 'Asia/Tokyo'    AS tokyo,
  TIMESTAMPTZ '2026-09-24 15:00:00+08' AT TIME ZONE 'Asia/Kolkata'  AS kolkata;

        by_name        |    by_offset
-----------------------+-----------------------
 2026-09-24 15:00:00   | 2026-09-24 15:00:00

       shanghai        |        tokyo        |       kolkata
-----------------------+---------------------+-----------------------
 2026-09-24 15:00:00   | 2026-09-24 16:00:00 | 2026-09-24 12:30:00

注意 Asia/Kolkata 是 +05:30 这种半小时偏移的时区，说明偏移不总是整小时。

用 pg_timezone_names 可以查证时区名是否合法及其当前偏移：

SELECT name, abbrev, utc_offset, is_dst
FROM pg_timezone_names
WHERE name IN ('Asia/Shanghai', 'Asia/Tokyo', 'America/New_York')
ORDER BY name;

       name        | abbrev | utc_offset | is_dst
-------------------+--------+------------+--------
 America/New_York  | EDT    | -04:00:00  | t
 Asia/Shanghai     | CST    | 08:00:00   | f
 Asia/Tokyo        | JST    | 09:00:00   | f

表中 Asia/Shanghai 的缩写也是 CST，与 America/Chicago 的 CST 撞名——这就是必须用全名而非缩写的直接证据。

时区名还依赖系统时区数据库（tzdata）。查询一个拼错的时区名不会报错，而是被当成固定偏移解析，这是极隐蔽的坑：

-- 拼错时区名：不会报错，但结果错误（被当 POSIX 风格偏移解析）
SELECT TIMESTAMPTZ '2026-09-24 15:00:00+08' AT TIME ZONE 'Asia/Shanghi';

         timezone
--------------------------
 2026-09-24 07:00:00

上例里 PostgreSQL 把无法识别的名字当成了 POSIX 时区字符串处理，返回了一个「看起来合理但实际错误」的值。务必用 pg_timezone_names 校验时区名，或在应用层用常量白名单。

夏令时产生的重复时刻与不存在时刻

夏令时切换会在两个方向制造麻烦：

-- 秋季回拨：02:00 到 03:00 之间出现了两次（重复时刻）
-- 春季拨快：02:00 直接跳到 03:00，02:30 这类时刻不存在（空洞）
SET TimeZone = 'America/New_York';

SELECT
  TIMESTAMPTZ '2026-11-01 05:30:00+00' AT TIME ZONE 'America/New_York' AS fall_back,
  TIMESTAMPTZ '2026-03-08 07:30:00+00' AT TIME ZONE 'America/New_York' AS spring_fwd;

      fall_back      |     spring_fwd
---------------------+---------------------
 2026-11-01 01:30:00 | 2026-03-08 03:30:00

秋季回拨当天的 01:30 在纽约出现了两次（一次 -04、一次 -05），因此把墙上时间转回绝对时刻时会产生歧义。PostgreSQL 在遇到歧义时会选择其中一个（通常是较早的那个偏移）：

-- 有歧义的墙上时间：01:30 在回拨日出现两次
SELECT TIMESTAMP '2026-11-01 01:30:00' AT TIME ZONE 'America/New_York' AS ambiguous;
-- 结果：2026-11-01 05:30:00+00   （按 -04 解释，即较早的一次）

-- 不存在的墙上时间：02:30 在拨快日不存在
SELECT TIMESTAMP '2026-03-08 02:30:00' AT TIME ZONE 'America/New_York';
-- 结果：2026-03-08 07:30:00+00   （被当成 03:30 的 EDT 解释）

上例说明：把墙上时间转成绝对时刻时，PostgreSQL 无法保证语义正确，它只是按规则挑一个结果。因此凡是可能落在夏令时切换窗口的时间，都必须以 timestamptz（绝对时刻）存储与传输。

不采用夏令时的时区（如 Asia/Shanghai、Asia/Tokyo、UTC）不存在上述问题：

SELECT
  TIMESTAMP '2026-03-08 02:30:00' AT TIME ZONE 'Asia/Shanghai' AS cn,
  TIMESTAMP '2026-11-01 01:30:00' AT TIME ZONE 'Asia/Shanghai' AS cn2;

           cn            |          cn2
-------------------------+-------------------------
 2026-03-08 02:30:00+08  | 2026-11-01 01:30:00+08

实战示例

-- 示例库：demo
DROP TABLE IF EXISTS user_events;
CREATE TABLE user_events (
  id           BIGSERIAL PRIMARY KEY,
  user_id      BIGINT      NOT NULL,
  region       TEXT        NOT NULL,        -- cn / us / jp
  occurred_at  TIMESTAMPTZ NOT NULL,        -- 一律存绝对时刻
  local_date   TIMESTAMP   NOT NULL,        -- 该用户所在时区的墙上时间（冗余存储，便于核对）
  payload      TEXT
);

-- 造数：每个区域 200 条，覆盖夏令时切换日（美国 3/8 与 11/1）
INSERT INTO user_events (user_id, region, occurred_at, local_date)
SELECT
  1000 + i,
  (ARRAY['cn','us','jp'])[1 + (i % 3)],
  TIMESTAMPTZ '2026-01-01 00:00:00+00' + (i * INTERVAL '19 hours'),
  (TIMESTAMPTZ '2026-01-01 00:00:00+00' + (i * INTERVAL '19 hours'))
    AT TIME ZONE (ARRAY['Asia/Shanghai','America/New_York','Asia/Tokyo'])[1 + (i % 3)]
FROM generate_series(1, 600) AS i;

CREATE INDEX idx_user_events_occurred_at ON user_events (occurred_at);
ANALYZE user_events;

步骤 1：同一时刻在三个时区下的呈现

SELECT
  occurred_at,
  occurred_at AT TIME ZONE 'Asia/Shanghai'    AS cn_time,
  occurred_at AT TIME ZONE 'America/New_York' AS us_time,
  occurred_at AT TIME ZONE 'Asia/Tokyo'       AS jp_time
FROM user_events
WHERE region = 'cn'
ORDER BY occurred_at
LIMIT 3;

预期输出：

       occurred_at       |      cn_time       |      us_time       |      jp_time
-------------------------+--------------------+--------------------+--------------------
 2026-01-01 00:00:00+00  | 2026-01-01 08:00:00 | 2025-12-31 19:00:00 | 2026-01-01 09:00:00
 2026-01-01 19:00:00+00  | 2026-01-02 03:00:00 | 2026-01-01 14:00:00 | 2026-01-02 04:00:00
 2026-01-02 14:00:00+00  | 2026-01-02 22:00:00 | 2026-01-02 09:00:00 | 2026-01-02 23:00:00

三列对应的是同一个绝对时刻，只是用三个时区的钟表去看。任何一列都能还原出原始 occurred_at。

步骤 2：按用户所在时区的自然日分组统计

SELECT
  region,
  (occurred_at AT TIME ZONE
    CASE region
      WHEN 'cn' THEN 'Asia/Shanghai'
      WHEN 'us' THEN 'America/New_York'
      WHEN 'jp' THEN 'Asia/Tokyo'
    END)::date AS local_day,
  count(*) AS cnt
FROM user_events
WHERE occurred_at >= TIMESTAMPTZ '2026-03-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2026-03-10 00:00:00+00'
GROUP BY region, local_day
ORDER BY region, local_day
LIMIT 8;

 region |  local_day | cnt
--------+------------+-----
 cn     | 2026-03-01 |   1
 cn     | 2026-03-02 |   2
 cn     | 2026-03-03 |   1
 jp     | 2026-03-01 |   2
 jp     | 2026-03-02 |   1
 us     | 2026-03-01 |   1
 us     | 2026-03-08 |   2   -- 美国夏令时切换日

按自然日分组时必须先把 timestamptz 转成业务时区的墙上时间。若直接 occurred_at::date，会按会话时区取日期，结果随配置漂移。

步骤 3：校验冗余的 local_date 是否与绝对时刻一致

SELECT id, region, occurred_at, local_date
FROM user_events
WHERE local_date <> occurred_at AT TIME ZONE
      CASE region
        WHEN 'cn' THEN 'Asia/Shanghai'
        WHEN 'us' THEN 'America/New_York'
        WHEN 'jp' THEN 'Asia/Tokyo'
      END
LIMIT 5;

 id | region | occurred_at | local_date
----+--------+-------------+------------
（无行）

步骤 4：注意美国夏令时切换日的偏移变化

SET TimeZone = 'UTC';

SELECT
  occurred_at,
  occurred_at AT TIME ZONE 'America/New_York' AS ny_wall,
  to_char(occurred_at AT TIME ZONE 'America/New_York', 'OF') AS ny_offset
FROM user_events
WHERE region = 'us'
  AND occurred_at >= TIMESTAMPTZ '2026-03-08 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2026-03-09 00:00:00+00'
ORDER BY occurred_at
LIMIT 4;

       occurred_at       |      ny_wall       | ny_offset
-------------------------+--------------------+-----------
 2026-03-08 00:00:00+00  | 2026-03-07 19:00:00| -05
 2026-03-08 06:00:00+00  | 2026-03-08 01:00:00| -05
 2026-03-08 07:00:00+00  | 2026-03-08 03:00:00| -04   -- 02:00 被跳过
 2026-03-08 08:00:00+00  | 2026-03-08 04:00:00| -04

偏移从 -05 跳到 -04，中间没有 02:00 这一小时。这就是「不存在时刻」的直观呈现。

步骤 5：用 UTC 做跨时区的时间窗口查询

-- 业务要求：北京时间 2026-03-08 全天，转成 UTC 范围查询（索引友好）
SELECT count(*) AS cnt
FROM user_events
WHERE occurred_at >= (TIMESTAMP '2026-03-08 00:00:00' AT TIME ZONE 'Asia/Shanghai')
  AND occurred_at <  (TIMESTAMP '2026-03-09 00:00:00' AT TIME ZONE 'Asia/Shanghai');

 cnt
-----
   2

关键写法：把业务时区的自然日边界转成绝对时刻，再与 timestamptz 列比较。这样既能正确表达「北京时间的一天」，又能走 idx_user_events_occurred_at 索引。

注意事项

1. AT TIME ZONE 的方向由输入类型决定，不是由函数名：

   -- timestamptz → timestamp（减时区）
   SELECT TIMESTAMPTZ '2026-09-24 15:00+08' AT TIME ZONE 'UTC';
   -- timestamp → timestamptz（加时区）
   SELECT TIMESTAMP '2026-09-24 15:00' AT TIME ZONE 'UTC';

2. timestamp 不带时区信息，不能表示绝对时刻。存业务时间一律优先用 timestamptz：

   -- 不推荐
   created_at TIMESTAMP NOT NULL
   -- 推荐
   created_at TIMESTAMPTZ NOT NULL

3. 时区缩写有歧义，禁止在生产代码中使用。CST、IST、EST 都对应多个时区：

   -- 错误！
   AT TIME ZONE 'CST'
   -- 正确
   AT TIME ZONE 'Asia/Shanghai'

4. 时区名拼写错误不会报错，会被当 POSIX 偏移解析，产生静默错误：

   -- 不推荐：拼错也不报错
   AT TIME ZONE 'Asia/Shanghi'

   所有时区名都应通过 pg_timezone_names 校验或在配置层做白名单。

5. 数字偏移（'+08'）不含夏令时规则，只适合用于展示或临时计算，不适合长期存储：

   -- 有隐患：该偏移在夏令时期间会错 1 小时
   AT TIME ZONE '+08'

6. ::date 与 ::timestamp 的转换依赖会话时区，做日期分组前必须显式指定业务时区：

   -- 不推荐：结果随会话漂移
   SELECT occurred_at::date FROM user_events;
   -- 推荐
   SELECT (occurred_at AT TIME ZONE 'Asia/Shanghai')::date FROM user_events;

7. timestamptz 输出的偏移是 显示 属性，不是存储内容。想持久化原始偏移必须另存一列：

   -- 结果里的 +08 是按会话时区渲染出来的
   SELECT TIMESTAMPTZ '2026-09-24 15:00:00+08';

8. 夏令时切换窗口内的墙上时间有歧义或不存在，timestamp AT TIME ZONE 的结果不可完全信任（见详细说明）。

9. AT TIME ZONE 中的时区参数可以是表达式，但列上的表达式会阻断索引；应把时区计算放在常量或 WHERE 的右侧：

   -- 索引友好：右侧是常量表达式
   WHERE occurred_at >= (TIMESTAMP '2026-03-08 00:00:00' AT TIME ZONE 'Asia/Shanghai')

10. to_char(..., 'TZ') 输出的缩写同样有歧义，跨系统交换请改用 TZH:TZM 或 OF：

    SELECT to_char(TIMESTAMPTZ '2026-09-24 15:00+08', 'OF');   -- +08

11. SET TimeZone 是会话级设置，连接池复用连接时可能继承上一个请求的设置。应用层应在连接初始化时显式设置，不要依赖默认值。

12. 转换前后要检查类型是否真的变了，AT TIME ZONE 对 timestamp 与 timestamptz 的返回类型相反，写错侧会让整条链路偏移一个时区：

    SELECT pg_typeof(TIMESTAMPTZ '2026-09-24 15:00+08' AT TIME ZONE 'UTC');  -- timestamp
    SELECT pg_typeof(TIMESTAMP   '2026-09-24 15:00'   AT TIME ZONE 'UTC');  -- timestamptz

最佳实践

1. 存储层一律使用 timestamptz，让数据库内部统一以 UTC 保存绝对时刻，从根上消除时区歧义。
2. 应用层与数据库连接统一设置 TimeZone = 'UTC'，把时区转换收敛到「展示层」，让 SQL 逻辑与会话配置解耦：

   SET TimeZone = 'UTC';

3. 时区名一律使用 Area/Location 全名，把合法时区清单放进配置白名单，不要接受用户自由输入。
4. 按自然日/月分组时，先 AT TIME ZONE 到业务时区再 date_trunc：

   SELECT date_trunc('day', occurred_at AT TIME ZONE 'Asia/Shanghai') AS local_day, count(*)
   FROM user_events GROUP BY 1;

5. 时间范围查询把业务时区边界转成绝对时刻，让 timestamptz 列上的索引可用：

   WHERE occurred_at >= (TIMESTAMP '2026-03-08' AT TIME ZONE 'Asia/Shanghai')
     AND occurred_at <  (TIMESTAMP '2026-03-09' AT TIME ZONE 'Asia/Shanghai')

6. 不要在同一列里混存不同语义的时间。需要「用户本地时间」语义时，另开一列存 timestamp 并同时记录时区名。
7. timestamptz 的时间戳字段在 API 输出时统一用 ISO 8601 带偏移格式（如 2026-09-24T15:00:00+08:00），让消费方不依赖任何隐式约定。
8. 跨时区调度与对账一律以 UTC 为基准，只在最终展示时转换；「北京时间的一天」这类业务概念要在查询层显式表达。
9. 升级系统 tzdata 后要回归测试时间相关逻辑，时区规则（尤其是夏令时起止日期）会随政府决策变化。
10. 对涉及夏令时的业务写专门测试，覆盖春季「不存在的时刻」与秋季「重复的时刻」两个边界。
11. SET TimeZone 放在事务/会话开始处，不要在中途改动，避免同一事务内出现两种渲染口径。
12. 日志与审计表中的时间列统一用 timestamptz 并显式记录 UTC，方便跨区域排查时不产生歧义。

下一节

继续学习：OVERLAPS 时间重叠

参考链接

• AT TIME ZONE — PostgreSQL 18 官方文档
• Time Zones
• Date/Time Types (timestamp vs timestamptz)
• pg_timezone_names
• Date/Time Configuration (TimeZone)