日期时间类型选型指南

本节把前面五节讲过的日期时间类型汇总成一张可执行的决策表。你将学会按「业务语义 → 时区需求 → 精度要求 → 存储成本」四个维度快速选定类型，并避开最常见的选型错误。

前置知识

阅读本节前，建议先了解：日期时间格式化

基础概念

PostgreSQL 的日期时间类型共 8 个，选型的第一刀是区分「记录绝对时刻」还是「记录墙上时间」。

类型	存储空间	取值范围	含时区	典型用途
DATE	4 字节	4713 BC ~ 5874897 AD	无	生日、账单日、业务日期
TIME	8 字节	00:00:00 ~ 24:00:00	无	营业时间、班次时段
TIMETZ	12 字节	同上 + 固定偏移	固定偏移	几乎不应使用
TIMESTAMP	8 字节	4713 BC ~ 294276 AD	无	本地墙上时间、排班
TIMESTAMPTZ	8 字节	同上	会话时区	事件发生时刻（推荐默认）
INTERVAL	16 字节	-178000000 年 ~ 178000000 年	不适用	时长、有效期、周期
TIMESTAMP(0)	8 字节	同上，秒精度	无	省空间的日志时间
TIMESTAMPTZ(0)	8 字节	同上，秒精度	会话时区	省空间的日志时间

关键术语：

• 绝对时刻（Instant）：时间轴上一个点，全球唯一，与观察者无关。对应 TIMESTAMPTZ。
• 墙上时间（Wall Clock）：某个地点挂钟显示的时间，脱离时区无意义。对应 TIMESTAMP。
• 精度修饰（Precision）：TIMESTAMP(p) 中的 p，取值 0–6，默认 6（微秒）。
• 日期粒度（Date Granularity）：用 DATE 表示时不需要关心时分秒，天然避免时区换算。

核心判断依据只有一句话：这个时间点在全世界的同一瞬间发生吗？

• 是 → TIMESTAMPTZ。如订单创建、支付完成、日志写入、消息发送。
• 否 → TIMESTAMP 或 DATE。如"2026 年国庆假期从 10 月 1 日开始"、营业时间 09:00–22:00。

-- 同一个"日期"在两种类型下含义不同
SELECT
  TIMESTAMPTZ '2026-10-01 00:00:00+08' AS absolute_moment,
  TIMESTAMP   '2026-10-01 00:00:00'    AS wall_clock,
  DATE        '2026-10-01'             AS just_a_date;

     absolute_moment     |     wall_clock      | just_a_date
-------------------------+---------------------+-------------
 2026-10-01 00:00:00+08  | 2026-10-01 00:00:00 | 2026-10-01
(1 row)

语法与用法

四种常用声明写法：

-- 最简可运行示例：各类型各就其位
CREATE TABLE datetime_showcase (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  -- 事件发生时刻：绝对时间点，必须带时区
  created_at    TIMESTAMPTZ     NOT NULL DEFAULT now(),
  -- 业务日期：只需要年月日
  bill_date     DATE            NOT NULL,
  -- 营业时段：墙上时间，无时区含义
  open_at       TIME            NOT NULL,
  close_at      TIME            NOT NULL,
  -- 时长：有效期 30 天
  valid_for     INTERVAL        NOT NULL DEFAULT INTERVAL '30 days',
  -- 秒精度日志时间：省一半索引空间
  logged_at     TIMESTAMPTZ(0)  NOT NULL DEFAULT now()
);

CREATE TABLE

INSERT INTO datetime_showcase (bill_date, open_at, close_at, valid_for)
VALUES
  (DATE '2026-09-22', TIME '09:00', TIME '22:00', INTERVAL '30 days'),
  (DATE '2026-09-23', TIME '10:00', TIME '23:30', INTERVAL '1 month');

SELECT * FROM datetime_showcase;

 id |          created_at           | bill_date  | open_at | close_at | valid_for |     logged_at
----+-------------------------------+------------+---------+----------+-----------+---------------------
  1 | 2026-09-22 11:30:00.123456+08 | 2026-09-22 | 09:00:00| 22:00:00 | 30 days   | 2026-09-22 11:30:00+08
  2 | 2026-09-22 11:30:00.123456+08 | 2026-09-23 | 10:00:00| 23:30:00 | 1 mon     | 2026-09-22 11:30:00+08
(2 rows)

详细说明

决策流程一：TIMESTAMPTZ 还是 TIMESTAMP

这是最关键的一刀，判断错会导致跨时区数据错乱。

-- 同一个时刻，两种类型的实际差异
SET TimeZone TO 'Asia/Shanghai';
SELECT TIMESTAMPTZ '2026-09-22 10:00:00+08' AS tstz_in_sh;

SET TimeZone TO 'America/New_York';
-- TIMESTAMPTZ 随会话时区改变显示，指向的仍是同一时刻
SELECT TIMESTAMPTZ '2026-09-22 10:00:00+08' AS tstz_in_ny,
       TIMESTAMP   '2026-09-22 10:00:00'    AS ts_always_fixed;
RESET TimeZone;

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

      tstz_in_ny       |    ts_always_fixed
-----------------------+---------------------
 2026-09-21 22:00:00-04 | 2026-09-22 10:00:00
(1 row)

TIMESTAMPTZ 显示的时钟读数变了，但代表的时刻没变；TIMESTAMP 显示的读数永远不变，但它到底代表哪个时刻取决于你怎么解释。

业务场景	推荐类型	理由
订单创建、支付、发货	TIMESTAMPTZ	绝对时刻，跨时区展示需要正确换算
用户注册、登录、操作日志	TIMESTAMPTZ	审计要求精确时刻
消息发送与接收	TIMESTAMPTZ	绝对时刻，客户端按本地时区展示
定时任务触发时间	TIMESTAMPTZ	绝对时刻
门店营业时间 09:00–22:00	TIME	墙上时间，各门店按本地时间执行
排班表「周一早班」	TIME（配合星期）	墙上时间
法定节假日假期区间	DATE	日期粒度，无需换算
会员生日	DATE	日期粒度
账单周期起止日	DATE	按业务日历计算

一个典型反例：把「每天早上 9 点推送」存成 TIMESTAMPTZ。

-- 错误！把业务规则存成绝对时刻，时区一改推送时间就错了
CREATE TABLE push_schedule_bad (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  push_at    TIMESTAMPTZ NOT NULL  -- 存了 '2026-09-22 09:00:00+08'
);

-- 正确：墙上时间用 TIME，配合业务时区在应用层计算实际触发时刻
CREATE TABLE push_schedule (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  push_time  TIME        NOT NULL DEFAULT TIME '09:00',
  timezone   TEXT        NOT NULL DEFAULT 'Asia/Shanghai'
);

决策流程二：需要 DATE 还是 TIMESTAMP

当业务只关心「哪一天」时，用 DATE 比 TIMESTAMP 更安全。

-- DATE 存储比 TIMESTAMP 省一半空间，且没有时区歧义
SELECT
  pg_column_size(DATE '2026-09-22')                  AS date_size,
  pg_column_size(TIMESTAMP '2026-09-22 00:00:00')    AS ts_size,
  pg_column_size(TIMESTAMPTZ '2026-09-22 00:00:00+08') AS tstz_size,
  pg_column_size(TIME '09:00:00')                    AS time_size,
  pg_column_size(INTERVAL '30 days')                 AS interval_size;

 date_size | ts_size | tstz_size | time_size | interval_size
-----------+---------+-----------+-----------+---------------
         4 |       8 |         8 |         8 |            16
(1 row)

关键判断：如果这个字段需要做「同一天」的比较，就必须用 DATE。

-- 错误！用 TIMESTAMP 存"生日"，跨时区时会漂移到前一天
CREATE TABLE members_bad (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  birthday_ts TIMESTAMP  -- '1990-01-01 00:00:00' 在 UTC-5 显示为 1989-12-31
);

-- 正确：生日只需日期粒度
CREATE TABLE members (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  birthday  DATE NOT NULL
);

DATE 做「今天生日」查询也简单得多，无需处理时分秒边界：

-- DATE 类型的生日查询，逻辑直观
SELECT id, birthday
FROM members
WHERE EXTRACT(MONTH FROM birthday) = EXTRACT(MONTH FROM CURRENT_DATE)
  AND EXTRACT(DAY   FROM birthday) = EXTRACT(DAY   FROM CURRENT_DATE);

决策流程三：精度怎么定

TIMESTAMP 默认精度 6（微秒），但绝大多数业务不需要微秒。

-- 精度修饰对存储与显示的影响
SELECT
  TIMESTAMPTZ '2026-09-22 10:30:45.123456+08'              AS default_p6,
  TIMESTAMPTZ(3) '2026-09-22 10:30:45.123456+08'           AS p3_milli,
  TIMESTAMPTZ(0) '2026-09-22 10:30:45.123456+08'           AS p0_second;

          default_p6           |        p3_milli         |       p0_second
-------------------------------+-------------------------+-------------------------
 2026-09-22 10:30:45.123456+08 | 2026-09-22 10:30:45.123+08 | 2026-09-22 10:30:45+08
(1 row)

精度只影响显示与舍入，存储空间始终是 8 字节（除 TIME 因历史原因 12 字节外）。所以降低精度的收益在于：

• 数据可读性更好，避免无意义的 .000000。
• 与外部系统对接时减少舍入差异（如 Java 的 LocalDateTime 默认毫秒）。
• 唯一索引冲突检测更符合业务预期（同一秒内的重复写入会被拦截）。

-- 用秒精度做唯一约束，避免同一秒内的重复请求
CREATE TABLE api_calls (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  api_key    TEXT          NOT NULL,
  called_at  TIMESTAMPTZ(0) NOT NULL DEFAULT now(),
  UNIQUE (api_key, called_at)
);

应用场景	推荐精度	说明
用户注册、订单	(0) 或 (3)	秒或毫秒足够
审计日志	(3)	毫秒，便于排序
性能监控、追踪	(6)	微秒，需要精细区分
金融对账	(3) 或 (6)	按交易所规范

决策流程四：时区策略怎么定

即使全部用 TIMESTAMPTZ，还存在「集群时区怎么配」和「业务时区怎么存」两个决策。

-- 查看当前集群与会话的设置
SHOW TimeZone;
SHOW log_timezone;

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

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

三种时区策略对比：

策略	集群 TimeZone	业务时区存储	优点	缺点
统一 UTC	UTC	应用层维护	无误导，日志与数据一致	直接查询结果需心算偏移
统一本地	Asia/Shanghai	隐含为集群时区	单地域业务直观	跨地域迁移时易出事故
混合	UTC	表里加 timezone 列	灵活支持多时区业务	查询需 AT TIME ZONE 转换

多地域业务的推荐做法是「集群用 UTC，业务时区落库」：

CREATE TABLE global_orders (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no     TEXT          NOT NULL UNIQUE,
  amount       NUMERIC(12,2) NOT NULL,
  -- 绝对时刻
  created_at   TIMESTAMPTZ   NOT NULL DEFAULT now(),
  -- 买家所在时区，用于本地化展示与报表
  buyer_tz     TEXT          NOT NULL DEFAULT 'Asia/Shanghai'
);

CREATE TABLE

INSERT INTO global_orders (order_no, amount, created_at, buyer_tz)
VALUES
  ('G-001', 199.00, TIMESTAMPTZ '2026-09-22 10:30:00+08', 'Asia/Shanghai'),
  ('G-002', 299.00, TIMESTAMPTZ '2026-09-21 22:30:00-04', 'America/New_York');

-- 按买家本地日期统计
SELECT
  buyer_tz,
  (created_at AT TIME ZONE buyer_tz)::DATE AS local_date,
  SUM(amount)                             AS total
FROM global_orders
GROUP BY buyer_tz, (created_at AT TIME ZONE buyer_tz)::DATE
ORDER BY buyer_tz, local_date;

     buyer_tz      | local_date | total
-------------------+------------+--------
 America/New_York  | 2026-09-21 | 299.00
 Asia/Shanghai     | 2026-09-22 | 199.00
(2 rows)

AT TIME ZONE 的右侧可以是列而不是字面量，这让「按用户时区归日」成为纯粹的 SQL 问题。

决策流程五：存储成本对比

在千万级以上的表里，日期时间列的类型选择会实际影响索引大小与缓存命中率。

类型	单列字节	1000 万行裸数据	加 B-tree 索引后
DATE	4	约 38 MB	约 190 MB
TIMESTAMP(0)	8	约 76 MB	约 270 MB
TIMESTAMPTZ	8	约 76 MB	约 270 MB
INTERVAL	16	约 152 MB	不宜直接建索引
文本 TEXT 存 ISO 串	约 26 变长	约 250 MB	约 400 MB

-- 实际测量单列大小
SELECT
  pg_column_size(DATE '2026-09-22')::TEXT                          AS date_col,
  pg_column_size(TIMESTAMPTZ '2026-09-22 10:30:45+08')::TEXT       AS tstz_col,
  pg_column_size(TIMESTAMPTZ(0) '2026-09-22 10:30:45+08')::TEXT    AS tstz0_col,
  pg_column_size('2026-09-22T10:30:45+08:00'::TEXT)::TEXT          AS text_col;

 date_col | tstz_col | tstz0_col | text_col
----------+----------+-----------+----------
 4        | 8        | 8         | 26
(1 row)

结论：降低精度不省存储，但用 DATE 替代 TIMESTAMP 能省一半，用原生类型替代文本能省 3 倍以上。

决策流程六：按业务场景速查

业务字段	推荐类型	前置处理
订单创建时间	TIMESTAMPTZ	应用层传 UTC
订单完成日期（报表用）	生成列 (created_at AT TIME ZONE 'Asia/Shanghai')::DATE	见注意事项
会员生日	DATE	校验 ≥ 1900-01-01
合同生效日 / 到期日	DATE	到期日用 DATE + INTERVAL 计算
试用期时长	INTERVAL	或用整数存天数
门店营业时间	TIME	加 CHECK 约束 open < close
任务执行耗时	INTERVAL	长耗时任务固化秒数建索引
日志写入时间	TIMESTAMPTZ(3)	毫秒足够
定时推送时刻（每日 9 点）	TIME + timezone 列	不要用 TIMESTAMPTZ
数据保留截止日	DATE	分区键常用

与 MySQL 的对比

从 MySQL 迁移时最容易踩的类型映射坑：

MySQL 类型	语义	PostgreSQL 对应	迁移注意
DATETIME	墙上时间，无时区	TIMESTAMP（无时区）	名字相同但 PG 的 TIMESTAMP 无时区
TIMESTAMP	自动 UTC 换算，范围到 2038	TIMESTAMPTZ	PG 无 2038 限制
DATE	日期	DATE	一致
TIME	时间	TIME	一致
YEAR	年份	SMALLINT 或 DATE	PG 无 YEAR 类型
TINYINT(1) 当布尔	0/1	BOOLEAN	需要转换
INT UNSIGNED	无符号	INTEGER + CHECK 或 BIGINT	PG 无无符号整型

-- 迁移前先确认源库时间列的语义
-- MySQL: SELECT @@time_zone; SHOW VARIABLES LIKE '%time_zone%';
-- 如果 MySQL 用 TIMESTAMP 且服务器时区非 UTC，导入前必须转换

-- 迁移检查清单（SQL 注释形式，实际在应用层执行）
-- 1. MySQL 的 DATETIME -> PG 的 TIMESTAMP（保持墙上时间语义）
-- 2. MySQL 的 TIMESTAMP -> PG 的 TIMESTAMPTZ（保持绝对时刻语义）
-- 3. 显式 SET TimeZone TO 'UTC' 后再导出，避免双重换算

一个具体陷阱：MySQL 的 TIMESTAMP 在会话时区不同时返回值会变，而 PG 的 TIMESTAMP 不会。直接按名字映射会丢失语义。

常见错误选型对照

错误做法	问题	影响	正确做法
用 TEXT 存时间	无法范围优化、格式不一致	查询慢、脏数据	TIMESTAMPTZ / DATE
用 TIMESTAMP 存事件时刻	无时区语义	跨时区错乱	TIMESTAMPTZ
用 TIMESTAMPTZ 存营业时间	业务规则被固化成时刻	时区一改就错	TIME + timezone
用 TIMESTAMP 存生日	时区换算时漂移	生日差一天	DATE
用 VARCHAR(19) 存 '2026-09-22 10:30:45'	字符串比较	排序错、索引失效	原生类型
用 INTEGER 存 Unix 秒	可读性差、需转换	排障困难	TIMESTAMPTZ（内部同为整数）
所有列都写 TIMESTAMPTZ(6)	无意义精度	对接舍入差异	按业务定 (0) 或 (3)
用 TIMETZ 存带时区时间	只支持固定偏移	无法处理夏令时	TIMETZ 几乎不用
用两列 date + time 拆分	查询需拼接、无法整体比较	代码复杂	单列 TIMESTAMPTZ

实战示例

为一个「全球会议预约系统」完成完整选型，从需求分析到建表、查询、验证。

第 1 步：明确需求与数据特征

假设系统需求如下：

• 用户在不同时区创建会议，会议开始时间必须是绝对时刻。
• 会议时长在 15 分钟到 8 小时之间。
• 每个用户有默认时区，用于展示与提醒。
• 需要按用户本地日期统计「每天有几场会议」。
• 会议有有效期（预约后 24 小时内可免费取消）。

第 2 步：按决策流程逐列选型

字段	语义	选定类型	决策依据
starts_at	会议开始绝对时刻	TIMESTAMPTZ	决策流程一
duration	会议时长	INTERVAL	需要 + 运算且不关心日历语义
organizer_tz	组织者时区	TEXT	IANA 名称，配 CHECK
free_cancel_until	免费取消截止	生成列 starts_at - INTERVAL '24 hours'	决策流程三，避免手工维护
meeting_date	本地日期（报表用）	生成列 (starts_at AT TIME ZONE organizer_tz)::DATE	避免每次查询重复计算

第 3 步：建表

CREATE TABLE meetings (
  id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title           TEXT        NOT NULL,
  -- 绝对时刻，用 TIMESTAMPTZ
  starts_at       TIMESTAMPTZ NOT NULL,
  -- 时长，用 INTERVAL 并约束合理范围
  duration        INTERVAL    NOT NULL
      CHECK (duration >= INTERVAL '15 minutes' AND duration <= INTERVAL '8 hours'),
  -- 组织者时区，IANA 名称
  organizer_tz    TEXT        NOT NULL DEFAULT 'Asia/Shanghai',

  -- 生成列：会议结束时刻，免去应用层计算
  ends_at         TIMESTAMPTZ
      GENERATED ALWAYS AS (starts_at + duration) STORED,

  -- 生成列：免费取消截止，预约后 24 小时
  free_cancel_until TIMESTAMPTZ
      GENERATED ALWAYS AS (starts_at - INTERVAL '24 hours') STORED,

  -- 生成列：组织者本地日期，便于按日统计
  local_date      DATE
      GENERATED ALWAYS AS ((starts_at AT TIME ZONE organizer_tz)::DATE) STORED,

  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- 时间范围合法性
  CONSTRAINT chk_order CHECK (ends_at > starts_at)
);

CREATE TABLE

生成列把重复计算固化下来，查询时可以像普通列一样直接用，也能建索引。

第 4 步：插入跨时区数据

INSERT INTO meetings (title, starts_at, duration, organizer_tz)
VALUES
  ('上海团队周会',   TIMESTAMPTZ '2026-09-22 09:00:00+08', INTERVAL '1 hour',  'Asia/Shanghai'),
  ('纽约客户评审',   TIMESTAMPTZ '2026-09-22 14:00:00-04', INTERVAL '90 minutes', 'America/New_York'),
  ('伦敦架构讨论',   TIMESTAMPTZ '2026-09-22 16:30:00+01', INTERVAL '2 hours', 'Europe/London'),
  ('东京产品同步',   TIMESTAMPTZ '2026-09-23 08:00:00+09', INTERVAL '45 minutes', 'Asia/Tokyo'),
  ('跨年倒计时彩排', TIMESTAMPTZ '2026-12-31 22:00:00+08', INTERVAL '3 hours', 'Asia/Shanghai');

INSERT 0 5

第 5 步：验证生成列与跨时区换算

SET TimeZone TO 'UTC';
SELECT
  title,
  starts_at,
  ends_at,
  duration,
  organizer_tz,
  local_date
FROM meetings
ORDER BY starts_at;

      title       |       starts_at        |        ends_at         | duration  |   organizer_tz    | local_date
------------------+------------------------+------------------------+-----------+-------------------+------------
 上海团队周会     | 2026-09-22 01:00:00+00 | 2026-09-22 02:00:00+00 | 01:00:00  | Asia/Shanghai     | 2026-09-22
 纽约客户评审     | 2026-09-22 18:00:00+00 | 2026-09-22 19:30:00+00 | 01:30:00  | America/New_York  | 2026-09-22
 伦敦架构讨论     | 2026-09-22 15:30:00+00 | 2026-09-22 17:30:00+00 | 02:00:00  | Europe/London     | 2026-09-22
 东京产品同步     | 2026-09-22 23:00:00+00 | 2026-09-22 23:45:00+00 | 00:45:00  | Asia/Tokyo        | 2026-09-23
 跨年倒计时彩排   | 2026-12-31 14:00:00+00 | 2026-12-31 17:00:00+00 | 03:00:00  | Asia/Shanghai     | 2026-12-31
(5 rows)

观察几个关键点：

• 会话时区设为 UTC，所有时刻统一显示为 UTC，但绝对时刻未变。
• local_date 是生成列，在插入时按 organizer_tz 计算并固化，它不会随会话时区变化。
• 东京会议在 UTC 下是 9 月 22 日 23:00，但 local_date 是 9 月 23 日——这正是生成列的价值。

-- 验证：会话时区切换后 local_date 不变
SET TimeZone TO 'Asia/Tokyo';
SELECT title, starts_at, local_date FROM meetings WHERE title = '上海团队周会';
RESET TimeZone;

     title     |       starts_at        | local_date
---------------+------------------------+------------
 上海团队周会  | 2026-09-22 10:00:00+09 | 2026-09-22
(1 row)

第 6 步：按组织者本地日期统计

SELECT
  organizer_tz,
  local_date,
  COUNT(*)                AS meeting_cnt,
  SUM(duration)           AS total_duration
FROM meetings
GROUP BY organizer_tz, local_date
ORDER BY local_date, organizer_tz;

   organizer_tz    | local_date | meeting_cnt | total_duration
-------------------+------------+-------------+----------------
 America/New_York  | 2026-09-22 |           1 | 01:30:00
 Asia/Shanghai     | 2026-09-22 |           1 | 01:00:00
 Europe/London     | 2026-09-22 |           1 | 02:00:00
 Asia/Tokyo        | 2026-09-23 |           1 | 00:45:00
 Asia/Shanghai     | 2026-12-31 |           1 | 03:00:00
(5 rows)

第 7 步：判断当前是否可免费取消

SELECT
  title,
  starts_at,
  free_cancel_until,
  (now() < free_cancel_until) AS can_cancel_free
FROM meetings
ORDER BY starts_at;

      title       |       starts_at        |   free_cancel_until    | can_cancel_free
------------------+------------------------+------------------------+-----------------
 上海团队周会     | 2026-09-22 09:00:00+08 | 2026-09-21 09:00:00+08 | f
 纽约客户评审     | 2026-09-22 14:00:00-04 | 2026-09-21 14:00:00-04 | f
 伦敦架构讨论     | 2026-09-22 16:30:00+01 | 2026-09-21 16:30:00+01 | f
 东京产品同步     | 2026-09-23 08:00:00+09 | 2026-09-22 08:00:00+09 | f
 跨年倒计时彩排   | 2026-12-31 22:00:00+08 | 2026-12-30 22:00:00+08 | t
(5 rows)

第 8 步：测量各类型实际占用

SELECT
  pg_column_size(starts_at)::TEXT        AS tstz_bytes,
  pg_column_size(duration)::TEXT         AS interval_bytes,
  pg_column_size(local_date)::TEXT       AS date_bytes,
  pg_column_size(organizer_tz)::TEXT     AS tz_text_bytes
FROM meetings
LIMIT 1;

 tstz_bytes | interval_bytes | date_bytes | tz_text_bytes
------------+----------------+------------+---------------
 8          | 16             | 4          | 18
(1 row)

第 9 步：清理环境

DROP TABLE meetings;
DROP TABLE global_orders;
DROP TABLE datetime_showcase;
DROP TABLE members;
DROP TABLE api_calls;
DROP TABLE push_schedule;
DROP TABLE push_schedule_bad;
DROP TABLE members_bad;

DROP TABLE

注意事项

• TIMESTAMP 与 TIMESTAMPTZ 名字极具误导性：TIMESTAMP 其实等价于 TIMESTAMP WITHOUT TIME ZONE，TIMESTAMPTZ 等价于 TIMESTAMP WITH TIME ZONE。而 TIMESTAMPTZ 不存储时区，只存 UTC 时刻。建表时建议写全称，避免团队误解。

• TIMETZ 几乎不应使用：它只记录固定 UTC 偏移，无法表达夏令时规则。而且它的比较运算行为反直觉——比较的是本地时间部分，忽略偏移。

  -- TIMETZ 的比较不看偏移，容易踩坑
  SELECT
    TIME WITH TIME ZONE '10:00:00+08' < TIME WITH TIME ZONE '10:00:00+00' AS compare_result;

   compare_result
  ----------------
   f
  (1 row)

  两个不同时刻被判定为相等，这就是不应使用 TIMETZ 的原因。

• 生成列中真正会踩坑的是「隐式类型转换」，不是 AT TIME ZONE：timezone(text, timestamptz) 与 timezone(text, timestamp) 这两个函数在系统目录 pg_proc 里的易变性标记都是 i（即 IMMUTABLE），所以 AT TIME ZONE 的第二参数即使是列也没问题，可以正常建生成列。上面实战示例中的 organizer_tz 版本在 PostgreSQL 18 上是合法的：

  -- 合法：timezone(text, timestamptz) 是 IMMUTABLE，第二参数是列也可以
  CREATE TABLE t_ok (
    ts  TIMESTAMPTZ,
    tz  TEXT,
    d   DATE GENERATED ALWAYS AS ((ts AT TIME ZONE tz)::DATE) STORED
  );

  CREATE TABLE

  真正会报 generation expression is not immutable 的，是把 numeric 隐式转成 text 的字符串拼接。EXTRACT 的返回类型是 numeric，而 numeric → text 的转换依赖会话的 lc_numeric 设置，因此不是 IMMUTABLE：

  -- 错误！EXTRACT 返回 numeric，隐式转 text 依赖 locale，不是 IMMUTABLE
  CREATE TABLE t_bad (
    created_at TIMESTAMPTZ,
    week_key   TEXT GENERATED ALWAYS AS (
      EXTRACT(isoyear FROM created_at AT TIME ZONE 'Asia/Shanghai') || '-W' ||
      EXTRACT(week    FROM created_at AT TIME ZONE 'Asia/Shanghai')
    ) STORED
  );

  ERROR:  generation expression is not immutable

  加上显式 ::text 转换后，转换在解析期就固定下来，生成列即可创建成功：

  CREATE TABLE t_good (
    created_at TIMESTAMPTZ,
    week_key   TEXT GENERATED ALWAYS AS (
      EXTRACT(isoyear FROM created_at AT TIME ZONE 'Asia/Shanghai')::text || '-W' ||
      EXTRACT(week    FROM created_at AT TIME ZONE 'Asia/Shanghai')::text
    ) STORED
  );

  CREATE TABLE

  写生成列遇到 generation expression is not immutable 时，优先检查表达式里有没有依赖 locale 或会话状态的隐式转换（numeric/date/timestamp 转 text 都是常见元凶），而不是先怀疑时区运算。

• INTERVAL 不能直接建 B-tree 索引做范围查询优化：INTERVAL 的比较会考虑日历语义（1 month 与 30 days 不等），排序结果与直觉不同。需要按秒数排序或建索引时，用生成列固化：

  CREATE TABLE tasks (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    duration    INTERVAL NOT NULL,
    duration_s  BIGINT GENERATED ALWAYS AS (EXTRACT(EPOCH FROM duration)::BIGINT) STORED
  );
  CREATE INDEX idx_tasks_duration_s ON tasks (duration_s);

• DATE 与 TIMESTAMPTZ 比较时会隐式转换：DATE 被当作会话时区的零点，结果依赖会话设置。

  SET TimeZone TO 'Asia/Shanghai';
  SELECT DATE '2026-09-22' = TIMESTAMPTZ '2026-09-22 00:00:00+08' AS sh_result;
  
  SET TimeZone TO 'UTC';
  SELECT DATE '2026-09-22' = TIMESTAMPTZ '2026-09-22 00:00:00+08' AS utc_result;
  RESET TimeZone;

   sh_result
  -----------
   t
  (1 row)
  
   utc_result
  ------------
   f
  (1 row)

  这是隐式转换的典型陷阱：不要在 WHERE 里混用 DATE 与 TIMESTAMPTZ，显式转换后再比较。

• 不要用两列 DATE + TIME 拆分存一个时间点：查询时需要拼接，无法用单列索引做范围扫描，且容易漏掉其中一个字段的约束。

• now() 在事务内返回固定值：now() 等价于 transaction_timestamp()，同一事务内多次调用结果相同。需要真实流逝时间用 clock_timestamp()。

  BEGIN;
  SELECT now() AS t1, clock_timestamp() AS c1;
  SELECT pg_sleep(0.5);
  SELECT now() AS t2, clock_timestamp() AS c2;
  COMMIT;

               t1              |             c1
  -----------------------------+-------------------------------
   2026-09-22 10:30:45.100+08  | 2026-09-22 10:30:45.100+08
  (1 row)
  
   pg_sleep
  ----------
  
  (1 row)
  
               t2              |             c2
  -----------------------------+-------------------------------
   2026-09-22 10:30:45.100+08  | 2026-09-22 10:30:45.603+08
  (1 row)

  now() 两次相同，clock_timestamp() 相差约 0.5 秒。

• CURRENT_DATE / CURRENT_TIMESTAMP 依赖会话时区，不要在跨时区业务里用它们做「业务日期」判断。

  -- 依赖会话时区，结果不稳定
  SELECT CURRENT_DATE;
  
  -- 稳定写法：显式指定业务时区
  SELECT (now() AT TIME ZONE 'Asia/Shanghai')::DATE AS biz_date;

最佳实践

• 默认用 TIMESTAMPTZ，只有明确是墙上时间才用 TIMESTAMP / TIME。这条规则能规避 90% 的时区问题。

• 集群 TimeZone 统一设为 UTC：

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

  这样日志与数据库内部一致，跨地域迁移无需调整。

• 业务时区作为数据落库，不要硬编码在 SQL 里。加一列 timezone TEXT，让查询参数化：

  SELECT id, order_no
  FROM global_orders
  WHERE (created_at AT TIME ZONE buyer_tz)::DATE = :local_date;

• 报表用日期维度优先用 DATE，且尽量用「半开区间」而不是 BETWEEN：

  SELECT COUNT(*)
  FROM global_orders
  WHERE created_at >= :start_date::TIMESTAMPTZ
    AND created_at <  (:start_date::DATE + 1)::TIMESTAMPTZ;

• 用 CHECK 约束守住时间范围合理性，把脏数据挡在写入阶段：

  ALTER TABLE meetings
    ADD CONSTRAINT chk_starts_at_sane
    CHECK (starts_at >= TIMESTAMPTZ '2000-01-01 00:00:00+00'
       AND starts_at <  TIMESTAMPTZ '2100-01-01 00:00:00+00');

• 日志与审计表用 TIMESTAMPTZ(3)：毫秒精度足够，且与 Java / Go 默认时间精度一致，减少对接时的舍入噪音。

• 需要按本地日期频繁分组时，用「常量时区」生成列 + 索引：

  CREATE TABLE events (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    occurred_at TIMESTAMPTZ NOT NULL,
    biz_date   DATE GENERATED ALWAYS AS ((occurred_at AT TIME ZONE 'Asia/Shanghai')::DATE) STORED
  );
  CREATE INDEX idx_events_biz_date ON events (biz_date, occurred_at);

  这样 WHERE biz_date = :d 能直接命中索引，无需在查询里做时区换算。

• 时区名称一律用 IANA 全名（Asia/Shanghai、America/New_York），不用缩写（CST 有歧义）、不用固定偏移（+08 无法处理夏令时）。可用 pg_timezone_names 校验：

  SELECT name
  FROM pg_timezone_names
  WHERE name IN ('Asia/Shanghai', 'America/New_York');

• 迁移自 MySQL 时逐列核对语义，不要按类型名直接映射。特别是 MySQL 的 TIMESTAMP 对应 PG 的 TIMESTAMPTZ，而 MySQL 的 DATETIME 对应 PG 的 TIMESTAMP。

• 测试必须覆盖时区切换与跨年边界：夏令时切换日（3 月与 11 月第二个周日）、年末最后一周、闰年 2 月 29 日，这三类日期最容易暴露选型缺陷。

• 不要为省空间把时间存成 INTEGER 的 Unix 秒：TIMESTAMPTZ 内部就是 8 字节整数，省不下空间，却丢失了原生时间运算与格式化能力。

下一节

继续学习：布尔类型（BOOLEAN）

参考链接

• PostgreSQL 官方文档：日期/时间类型
• PostgreSQL 官方文档：日期/时间函数与操作符
• PostgreSQL 官方文档：数据类型格式化函数
• PostgreSQL 官方文档：服务器配置参数（timezone）
• PostgreSQL 官方文档：生成列
• PostgreSQL 官方文档：数据类型总览