时区与 TimeZone 设置

本节讲解 PostgreSQL 的时区体系。你将掌握 TimeZone 参数的取值与优先级、SET TIME ZONE 与 AT TIME ZONE 两种截然不同的用法、时区数据来源，以及夏令时与跨时区业务的设计要点。

前置知识

阅读本节前，建议先了解：INTERVAL 时间间隔

基础概念

时区（Time Zone）是地球上一组使用相同标准时间的区域。数据库中处理时区需要区分三个层次：

层次	含义	在 PostgreSQL 中的体现
存储层	数据如何落盘	TIMESTAMPTZ 转 UTC 微秒数存储
输入解释层	不带偏移的字符串按哪个时区理解	会话 TimeZone 参数
输出显示层	UTC 值按哪个时区渲染	会话 TimeZone 参数

关键术语：

• UTC（Coordinated Universal Time）：协调世界时，全球时间基准。
• UTC 偏移（UTC Offset）：本地时间与 UTC 之差，如 +08、-05。
• 时区名称（Time Zone Name）：如 Asia/Shanghai，由 IANA 时区数据库维护。
• 时区缩写（Time Zone Abbreviation）：如 CST、PST，存在歧义，不建议使用。
• 夏令时（DST, Daylight Saving Time）：部分时区在夏季把时钟拨快一小时。

PostgreSQL 不自己维护时区规则，而是使用操作系统的 IANA 时区数据库（/usr/share/zoneinfo）。这让它能正确处理历史与未来的夏令时变更。

-- 查看当前会话时区
SHOW TimeZone;

   TimeZone
---------------
 Asia/Shanghai
(1 row)

-- 查看服务端编译时确定的时区数据来源
SELECT name, abbrev, utc_offset, is_dst
FROM pg_timezone_names
WHERE name = 'Asia/Shanghai';

      name      | abbrev | utc_offset | is_dst
----------------+--------+------------+--------
 Asia/Shanghai  | CST    | 08:00:00   | f
(1 row)

语法与用法

设置时区有三种等价的写法：

-- 写法一：标准 SQL
SET TIME ZONE 'Asia/Shanghai';

-- 写法二：参数名写法（等价）
SET timezone TO 'Asia/Shanghai';

-- 写法三：只用偏移（不带夏令时规则）
SET TIME ZONE INTERVAL '+08:00' HOUR TO MINUTE;

-- 最简可运行示例
SET TimeZone TO 'UTC';
SELECT TIMESTAMPTZ '2026-09-22 10:30:00+08' AS in_utc;

SET TimeZone TO 'Asia/Shanghai';
SELECT TIMESTAMPTZ '2026-09-22 10:30:00+08' AS in_shanghai;

        in_utc
------------------------
 2026-09-22 02:30:00+00
(1 row)

       in_shanghai
------------------------
 2026-09-22 10:30:00+08
(1 row)

同一个时刻，仅因会话时区不同，输出文本就完全不同。可用 RESET 恢复默认：

RESET TimeZone;

详细说明

取值与优先级

TimeZone 是会话级参数，优先级从高到低为：

优先级	来源	设置方式	生效范围
1（最高）	会话	SET TimeZone TO ...	当前连接
2	角色	ALTER ROLE xxx SET TimeZone = ...	该角色所有连接
3	数据库	ALTER DATABASE demo SET TimeZone = ...	连到该库的所有连接
4	集群	postgresql.conf 的 timezone	全实例默认
5（最低）	系统	initdb 时读取的环境/系统时区	初始化时固化

查看各层当前值：

-- 当前会话生效值
SHOW TimeZone;
SELECT current_setting('TimeZone') AS session_tz;

-- 集群配置值（当前会话若覆盖则会不同）
SELECT name, setting, source
FROM pg_settings
WHERE name = 'TimeZone';

    name   |    setting    |        source
-----------+---------------+----------------------
 TimeZone  | Asia/Shanghai | session
(1 row)

source 常见取值：default（编译默认）、configuration file（postgresql.conf）、database、role、session、client。

注意：客户端可以在连接串里指定时区，它会成为最高优先级：

# JDBC：连接级指定时区
jdbc:postgresql://127.0.0.1:5432/demo?TimeZone=Asia/Shanghai

# libpq：环境变量或连接参数
PGTZ=Asia/Shanghai
options=-c timezone=Asia/Shanghai

AT TIME ZONE：转换而非设置

AT TIME ZONE 是表达式级的转换操作符，它不改变会话时区，只对当前表达式做一次时区换算。这是它与 SET TIME ZONE 最本质的区别。

两种用法方向相反，需要牢记：

输入类型	语法	输出类型	语义
TIMESTAMP（无时区）	ts AT TIME ZONE 'tz'	TIMESTAMPTZ	把墙上时间"解释"为某时区 → 得到绝对时刻
TIMESTAMPTZ	tstz AT TIME ZONE 'tz'	TIMESTAMP	把绝对时刻"渲染"到某时区 → 得到墙上时间
TIME WITH TIME ZONE	timetz AT TIME ZONE 'tz'	TIMETZ	同上的时间版本

-- 用法一：无时区的 timestamp → 有绝对时刻的 timestamptz
SELECT
  TIMESTAMP '2026-09-22 10:30:00' AT TIME ZONE 'Asia/Shanghai' AS as_shanghai,
  TIMESTAMP '2026-09-22 10:30:00' AT TIME ZONE 'UTC'            AS as_utc,
  TIMESTAMP '2026-09-22 10:30:00' AT TIME ZONE 'America/New_York' AS as_ny;

       as_shanghai       |         as_utc         |          as_ny
-------------------------+------------------------+-------------------------
 2026-09-22 10:30:00+08  | 2026-09-22 10:30:00+00 | 2026-09-22 10:30:00-04

同一个"墙上时间"被解释成三个不同的绝对时刻。

-- 用法二：有绝对时刻的 timestamptz → 各时区的墙上时间
SELECT
  TIMESTAMPTZ '2026-09-22 10:30:00+08' AT TIME ZONE 'Asia/Shanghai'    AS wall_sh,
  TIMESTAMPTZ '2026-09-22 10:30:00+08' AT TIME ZONE 'UTC'              AS wall_utc,
  TIMESTAMPTZ '2026-09-22 10:30:00+08' AT TIME ZONE 'America/New_York' AS wall_ny;

     wall_sh      |     wall_utc      |     wall_ny
------------------+-------------------+------------------
 2026-09-22 10:30:00 | 2026-09-22 02:30:00 | 2026-09-21 22:30:00

注意结果类型是 TIMESTAMP（没有 +08 后缀），因为它是"墙上时间"。

两种用法可以互相抵消，这也是一个有用的校验技巧：

SELECT
  (TIMESTAMPTZ '2026-09-22 10:30:00+08' AT TIME ZONE 'UTC') AT TIME ZONE 'UTC' AS round_trip;

      round_trip
------------------------
 2026-09-22 10:30:00+08
(1 row)

时区数据来源表

PostgreSQL 提供两张系统视图查询时区信息：

视图	内容	用途
pg_timezone_names	IANA 完整时区名（如 Asia/Shanghai）	精确、带夏令时规则，推荐
pg_timezone_abbrevs	时区缩写（如 CST、PST）	有歧义，仅兼容旧数据

-- 中国大陆常见时区
SELECT name, abbrev, utc_offset, is_dst
FROM pg_timezone_names
WHERE name IN ('Asia/Shanghai', 'Asia/Chongqing', 'Asia/Urumqi', 'Asia/Hong_Kong')
ORDER BY name;

       name       | abbrev | utc_offset | is_dst
------------------+--------+------------+--------
 Asia/Chongqing   | CST    | 08:00:00   | f
 Asia/Hong_Kong   | HKT    | 08:00:00   | f
 Asia/Shanghai    | CST    | 08:00:00   | f
 Asia/Urumqi      | +06    | 06:00:00   | f
(4 rows)

注意 Asia/Urumqi 是 +06——新疆在行政上使用北京时间，但 IANA 数据库里保留了当地历史时区。

-- 缩写视图：同一个缩写对应多个偏移，这就是"歧义"
SELECT abbrev, utc_offset, is_dst
FROM pg_timezone_abbrevs
WHERE abbrev = 'CST';

 abbrev | utc_offset | is_dst
--------+------------+--------
 CST    | -06:00:00  | f
(1 row)

-- 可查询所有含夏令时的时区
SELECT count(*) AS dst_zone_count
FROM pg_timezone_names
WHERE is_dst;

 dst_zone_count
----------------
            103
(1 row)

夏令时陷阱

夏令时是时区问题里最容易出错的场景。以美东为例：2026 年 3 月 8 日 02:00 时钟直接跳到 03:00。

SET TimeZone TO 'America/New_York';

-- 02:30 这个墙上时间在 3 月 8 日"不存在"
SELECT
  TIMESTAMP '2026-03-08 02:30:00' AT TIME ZONE 'America/New_York' AS nonexistent,
  TIMESTAMP '2026-03-08 03:30:00' AT TIME ZONE 'America/New_York' AS exists;

      nonexistent       |        exists
------------------------+------------------------
 2026-03-08 03:30:00-04 | 2026-03-08 03:30:00-04

不存在的 02:30 被"顺延"成 03:30——两个不同输入映射到同一时刻，这会造成数据丢失。

-- 夏令时切换导致某天只有 23 小时（秋季回拨那天有 25 小时）
SELECT
  (TIMESTAMP '2026-03-09 00:00:00' AT TIME ZONE 'America/New_York')
  - (TIMESTAMP '2026-03-08 00:00:00' AT TIME ZONE 'America/New_York') AS spring_day_len,
  (TIMESTAMP '2026-11-02 00:00:00' AT TIME ZONE 'America/New_York')
  - (TIMESTAMP '2026-11-01 00:00:00' AT TIME ZONE 'America/New_York') AS fall_day_len;

 spring_day_len | fall_day_len
----------------+--------------
 23:00:00       | 25:00:00

结论：不要用"当天 00:00 加 24 小时"来求次日 00:00，跨夏令时切换日会错一小时：

-- 错误！跨夏令时切换日会得到 01:00 或 23:00
SELECT TIMESTAMPTZ '2026-03-08 00:00:00-05' + INTERVAL '24 hours' AS wrong_next_day;

-- 正确：用"日期 + 1"再定锚到时区
SELECT (DATE '2026-03-09' + TIME '00:00:00') AT TIME ZONE 'America/New_York' AS right_next_day;

       wrong_next_day
------------------------
 2026-03-09 01:00:00-04
(1 row)

       right_next_day
------------------------
 2026-03-09 00:00:00-04
(1 row)

TimeZone 与 log_timezone

有两个容易混淆的参数：

参数	作用	建议
timezone	会话显示的时区	生产建议 UTC 或业务主时区
log_timezone	日志时间戳使用的时区	建议与 timezone 一致，便于排查

SELECT name, setting FROM pg_settings WHERE name IN ('TimeZone', 'log_timezone');

     name     |    setting
--------------+---------------
 TimeZone     | Asia/Shanghai
 log_timezone | Asia/Shanghai
(2 rows)

实战示例

场景：一个面向全球用户的订单系统，需要按用户所在时区统计当日订单，并在审计日志中保留 UTC 时刻。

第 1 步：建表

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no     TEXT        NOT NULL UNIQUE,
  buyer_tz     TEXT        NOT NULL,                    -- 买家所在时区
  amount       NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()        -- 统一存 UTC 时刻
);

第 2 步：插入跨时区数据

SET TimeZone TO 'UTC';

INSERT INTO orders (order_no, buyer_tz, amount, created_at) VALUES
  ('A001', 'Asia/Shanghai',    199.00, TIMESTAMPTZ '2026-09-21 23:30:00+00'),
  ('A002', 'Asia/Shanghai',    299.00, TIMESTAMPTZ '2026-09-22 01:00:00+00'),
  ('A003', 'America/New_York',  88.00, TIMESTAMPTZ '2026-09-22 03:00:00+00'),
  ('A004', 'America/New_York', 520.00, TIMESTAMPTZ '2026-09-22 13:00:00+00'),
  ('A005', 'Europe/London',    150.00, TIMESTAMPTZ '2026-09-22 22:30:00+00');

第 3 步：按"买家本地日期"统计

这是跨时区业务的经典需求：本地时间是 9 月 22 日，UTC 时间可能落在 21 日。

-- 错误示范：按 UTC 日期分组，会与买家的直觉不符
SELECT (created_at AT TIME ZONE 'UTC')::DATE AS utc_date, count(*) AS cnt
FROM orders GROUP BY 1 ORDER BY 1;

  utc_date  | cnt
------------+-----
 2026-09-21 |   1
 2026-09-22 |   4
(2 rows)

-- 正确做法：先换算到买家所在时区，再取日期
SELECT
  buyer_tz,
  (created_at AT TIME ZONE buyer_tz)::DATE AS local_date,
  count(*)                                AS order_count,
  sum(amount)                             AS total_amount
FROM orders
GROUP BY buyer_tz, local_date
ORDER BY buyer_tz, local_date;

    buyer_tz     | local_date | order_count | total_amount
-----------------+------------+-------------+--------------
 America/New_York| 2026-09-21 |           1 |        88.00
 America/New_York| 2026-09-22 |           1 |       520.00
 Asia/Shanghai   | 2026-09-22 |           2 |       498.00
 Europe/London   | 2026-09-22 |           1 |       150.00
(4 rows)

A003 在 UTC 是 9 月 22 日 03:00，但在纽约本地是 9 月 21 日 23:00——按本地日期统计时被归入 21 日。这就是"同一批数据两种分组结果不同"的根源。

第 4 步：展示时自动换算

-- 用 AT TIME ZONE 按买家时区展示，不依赖会话设置
SELECT
  order_no,
  buyer_tz,
  created_at                                        AS utc_time,
  created_at AT TIME ZONE buyer_tz                  AS buyer_local_time
FROM orders
ORDER BY created_at;

 order_no |    buyer_tz      |       utc_time        |  buyer_local_time
----------+------------------+-----------------------+---------------------
 A001     | Asia/Shanghai    | 2026-09-21 23:30:00+00 | 2026-09-22 07:30:00
 A002     | Asia/Shanghai    | 2026-09-22 01:00:00+00 | 2026-09-22 09:00:00
 A003     | America/New_York | 2026-09-22 03:00:00+00 | 2026-09-21 23:00:00
 A004     | America/New_York | 2026-09-22 13:00:00+00 | 2026-09-22 09:00:00
 A005     | Europe/London    | 2026-09-22 22:30:00+00 | 2026-09-22 23:30:00
(5 rows)

第 5 步：验证会话时区不影响结果

-- 切换会话时区后，AT TIME ZONE 的结果不变（因为它与输入类型是 timestamptz）
SET TimeZone TO 'America/Los_Angeles';
SELECT order_no, created_at AT TIME ZONE 'Asia/Shanghai' AS shanghai_time
FROM orders WHERE order_no = 'A001';

 order_no |  shanghai_time
----------+---------------------
 A001     | 2026-09-22 07:30:00
(1 row)

而直接 SELECT created_at 的结果会随会话时区变化：

SELECT order_no, created_at FROM orders WHERE order_no = 'A001';

 order_no |       created_at
----------+------------------------
 A001     | 2026-09-21 16:30:00-07
(1 row)

第 6 步：清理

RESET TimeZone;
DROP TABLE IF EXISTS orders;

注意事项

• AT TIME ZONE 与 SET TIME ZONE 完全不同。前者是表达式转换，后者是会话设置。混用会得到意料之外的结果。

-- 错误！BETWEEN 的右边界会随会话时区变化，导致结果不稳定
SELECT * FROM orders WHERE created_at BETWEEN '2026-09-22' AND '2026-09-23';

-- 正确：显式指定时区，或用参数化查询传入 timestamptz
SELECT * FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-09-22 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2026-09-23 00:00:00+08';

• 不要使用时区缩写。CST 可指中国标准时间（UTC+8）、美国中部时间（UTC-6）、古巴标准时间（UTC-5）。

-- 错误！含义歧义，且 PostgreSQL 会按 pg_timezone_abbrevs 解释
SELECT TIMESTAMP '2026-09-22 10:30:00' AT TIME ZONE 'CST';

-- 正确：使用 IANA 全名
SELECT TIMESTAMP '2026-09-22 10:30:00' AT TIME ZONE 'Asia/Shanghai';

• timestamptz 的列不要与 timestamp 直接比较后再指望时区换算。两者比较时 timestamp 会按会话时区被隐式转成 timestamptz。

• 夏令时切换日不要用"加 24 小时"计算次日，见上文演示。正确做法是基于日期运算再定锚：

-- 推荐：先算日期，再定锚到目标时区
SELECT ((created_at AT TIME ZONE 'America/New_York')::DATE + 1) AT TIME ZONE 'America/New_York' AS next_midnight;

• SET TIME ZONE 的偏移写法不携带夏令时规则。SET TIME ZONE '+08:00' 永远是 +08，而 SET TIME ZONE 'Asia/Shanghai' 会跟随 IANA 历史规则（1949 年之前上海曾用夏令时）。

-- 历史数据演示：上海曾在 1940 年代使用夏令时
SELECT name, utc_offset, is_dst
FROM pg_timezone_names
WHERE name = 'Asia/Shanghai';

• TimeZone 影响日期输入的解析。'2026-09-22 10:30:00'::TIMESTAMPTZ 会按会话时区解释。

SET TimeZone TO 'UTC';
SELECT '2026-09-22 10:30:00'::TIMESTAMPTZ AS as_utc;

SET TimeZone TO 'Asia/Shanghai';
SELECT '2026-09-22 10:30:00'::TIMESTAMPTZ AS as_shanghai;

        as_utc
------------------------
 2026-09-22 10:30:00+00
(1 row)

      as_shanghai
------------------------
 2026-09-22 10:30:00+08
(1 row)

同一字符串得到两个不同绝对时刻——输入不带偏移的字符串本身就是一个危险动作。

• 容器化部署时区默认是 UTC。Docker 镜像通常不装 tzdata 或默认 UTC，本地开发与生产环境时区不一致是最常见的"只在我机器上正常"问题来源。

# 检查容器内时区数据是否存在
docker exec -it pg18 ls /usr/share/zoneinfo/Asia/Shanghai
# 若不存在需要安装 tzdata
docker exec -it pg18 apk add --no-cache tzdata

• pg_timezone_names 的 is_dst 列表示"当前是否处于夏令时"而非"该时区是否有夏令时"。判断是否有夏令时要看历史上是否存在 is_dst = true 的记录。

最佳实践

• 存储层统一用 TIMESTAMPTZ，服务端与数据库时区统一设为 UTC。这样任何时刻的输入输出都是确定的。

# postgresql.conf
timezone = 'UTC'
log_timezone = 'UTC'

• 客户端把 TimeZone 显式设为业务时区，不要依赖服务端默认值：

# 连接串显式声明
jdbc:postgresql://db:5432/demo?TimeZone=Asia/Shanghai

• 需要"业务本地时间"时，额外存一列 DATE 或 TEXT 时区标识，避免每次都做换算：

-- 推荐：冗余存储本地日期，便于按天分区与统计
CREATE TABLE events (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at TIMESTAMPTZ NOT NULL,
  tz          TEXT        NOT NULL DEFAULT 'UTC',
  local_date  DATE        GENERATED ALWAYS AS ((occurred_at AT TIME ZONE tz)::DATE) STORED
);
CREATE INDEX idx_events_local_date ON events (local_date);

注意：生成列表达式必须 IMMUTABLE，而 AT TIME ZONE 在 timestamptz → timestamp 方向上是 STABLE 而非 IMMUTABLE，因此上面的生成列只在会话时区固定时成立，生产环境建议改为在应用层或触发器中填充。

• 按天分区时用 date_trunc('day', tstz AT TIME ZONE 'Asia/Shanghai') 得到本地日期边界，而不是用 UTC 边界。

• 跨时区聚合必须先 AT TIME ZONE 再 GROUP BY，见实战示例第 3 步。

• 不要在 SQL 里硬编码时区字符串，把时区作为配置或参数从应用层传入，便于统一调整。

• 测试覆盖夏令时切换日（每年 3 月与 11 月的第二个周日，美国时区），这两个日期最容易暴露缺陷。

• 备份与导出时间字段时显式 SET TimeZone TO 'UTC'，保证导出结果与运行环境无关。

下一节

继续学习：日期时间格式化

参考链接

• PostgreSQL 官方文档：日期/时间类型（时区章节）
• PostgreSQL 官方文档：日期/时间函数与操作符（AT TIME ZONE）
• PostgreSQL 官方文档：区域设置
• PostgreSQL 官方文档：服务器配置参数（timezone）