PostgreSQL timestamp 列里到底存了什么？

PostgreSQL 中的 timestamp 和 timestamptz 都以同一种方式存储：一个 64 位整数，表示距离 1970-01-01 00:00:00 UTC 的微秒数。区别仅在于数据格式化给人看时的行为。

为什么容易踩坑？

• 两个列、一个日期……却查出两个不同的结果
• 你的应用插入了 2025-07-29 10:00，但另一个团队看到的是 02:00
• 前端渲染的 ISO 字符串跟后端日志对不上

两罐桃子：一罐无标签，一罐有标签

数据类型	官方名称	存储值	SELECT 时的行为
timestamp	timestamp without time zone	原始微秒计数	原样返回 — Postgres 不做任何时区猜测
timestamptz	timestamp with time zone	同样的微秒计数	Postgres 在发送前应用会话的 TimeZone 设置

类比

• timestamp = 一罐没贴产地标签的桃子。你知道是水果，但不知道在哪儿罐装的。
• timestamptz = 一罐印着「产地 UTC+8」的桃子。打开的人可以自行决定是否换算。

底层原理：就是一个大数字

2000-01-01 00:00:00 UTC  → 0
2000-01-01 00:00:01 UTC  → 1 000 000

• 单位：微秒（百万分之一秒）
• 范围：公元前 4713 年 – 公元 294276 年
• timestamp 和 timestamptz 的存储完全一样；不同的只是解释方式

15 秒速览 Demo

-- 客户端使用上海时区
SET TimeZone = 'Asia/Shanghai';

CREATE TABLE demo (
  created_ts timestamp,
  created_tz timestamptz
);

INSERT INTO demo VALUES ('2025-07-29 10:00', '2025-07-29 10:00');

查询	结果	原因
SELECT created_ts FROM demo;	2025-07-29 10:00:00	原始值，不做时区运算
SELECT created_tz FROM demo;	2025-07-29 10:00:00+08	输出时贴上标签
SET TimeZone = 'UTC'; 后查询	2025-07-29 02:00:00+00	同一时刻，换个视角

时间戳运算与时间间隔

PostgreSQL 时间戳最实用的一面是时间间隔（interval）运算。两种类型存的都是微秒计数，所以可以直接加减 interval：

-- 加上 3 小时 30 分钟
SELECT '2025-07-29 10:00'::timestamptz + INTERVAL '3 hours 30 minutes';
-- → 2025-07-29 13:30:00+08

-- 求两个时间戳之差
SELECT '2025-07-30 09:00'::timestamptz - '2025-07-29 10:00'::timestamptz;
-- → 23:00:00  （结果是一个 interval）

-- 提取指定字段
SELECT EXTRACT(EPOCH FROM '2025-07-29 10:00:00+08'::timestamptz);
-- → 1753754400.000000  （以秒为单位的 Unix 时间戳；PostgreSQL 14 起为 numeric 类型）

-- 截断到当天零点（按天聚合时很有用）
SELECT date_trunc('day', '2025-07-29 15:42:19+08'::timestamptz);
-- → 2025-07-29 00:00:00+08

外部系统只认 Unix 纪元秒数时，EXTRACT(EPOCH FROM ...) 尤其好用。反过来，也可以把纪元秒数转回时间戳：

SELECT to_timestamp(1753754400);
-- → 2025-07-29 10:00:00+08  （会话时区为 Asia/Shanghai 时）

有一点不起眼却很重要：对 timestamp（不带时区）做 interval 运算会完全无视夏令时切换，timestamptz 则会把它考虑进去。所以给跨越夏令时边界的 timestamptz 值加上 INTERVAL '1 day'，会正确地得到同一个墙上时间，而不是恰好 24 小时之后。

索引与性能考量

timestamp 和 timestamptz 都以 8 字节整数存储，两者在存储和索引上没有性能差异。底层的比较只是整数比较，所以 B-tree 索引在两种类型上的表现完全相同。

不过，实际使用中有几点要注意：

• 范围查询：不论哪种类型，只要建了索引，WHERE created_at > '2025-07-01' 都能高效执行。对于 timestamptz，PostgreSQL 会先把字面量转换成 UTC 再比较，索引照样能用上。
• 分区键：对时间戳列做范围分区时，timestamptz 通常更安全，因为分区边界没有歧义（始终是 UTC）。换成 timestamp，'2025-07-01 00:00' 这样的边界在不同会话中可能含义不同。
• 函数索引：如果经常只按日期查询（不管具体时间），可以考虑在 date_trunc('day', created_at) 上建索引，加快按天聚合的查询。

常见陷阱与快速修复

1. 不同用户，不同时钟

• 原因：各客户端使用不同的 TimeZone 设置来读取 timestamptz
• 修复：要么全部用 timestamp 并约定统一时区，要么在连接初始化时强制 SET TimeZone = 'UTC'

应用代码中常见的做法，是在初始化连接池时统一设置一次时区：

-- 写在连接配置里（例如 pg 连接池配置）
SET timezone = 'UTC';

这样所有会话看到的都是同一种 UTC 表示，换算成本地时间来显示的工作交给应用层。

2. 用错类型存「墙上时间」

• 业务日历（营业时间、截止日期）应使用 timestamp
• 跨境业务流（订单、日志）应以 UTC 存入 timestamptz

判断方法很简单：要回答的是「这件事发生在哪个时刻？」，就用 timestamptz；要回答的是「墙上的钟显示几点？」，就用 timestamp。

3. API 漂移

• timestamptz 始终以 ISO-8601 带偏移量的字符串传输（Z 或 +08:00）
• 让前端 UI 根据用户本地时区格式化

4. 跨类型比较时间戳

在比较或 JOIN 中混用 timestamp 和 timestamptz，是隐蔽 bug 的常见来源：

-- 危险：隐式类型转换会套用会话时区
SELECT * FROM orders o
JOIN schedules s ON o.created_tz = s.start_ts;
-- PostgreSQL 会按会话时区把 s.start_ts 转换为 timestamptz
-- 不同会话可能得到不同的 JOIN 结果！

修复：跨类型比较时一律显式转换，或者在同一业务领域内统一只用一种类型。

5. ORM 默认值的陷阱

很多 ORM（Django、SQLAlchemy、ActiveRecord）默认使用不带时区的 timestamp。检查一下迁移文件：如果应用要服务不同时区的用户，就把默认类型改成 timestamptz。Django 在 settings 里设置 USE_TZ = True，SQLAlchemy 用 DateTime(timezone=True)。

速查表：该用哪个？

仅本地日历 → timestamp
涉及全球   → timestamptz（存 UTC）

• 财务报表、排课表 → timestamp
• 审计日志、电商订单 → timestamptz

用 Go Tools 快速验证

需求	工具	用法
查看 SQL 中的 epoch 值	时间戳转换器	粘贴 1753754400，点击转换
整理含时间字段的 JSON	JSON 格式化工具	粘贴数据，一键美化

所有工具完全在浏览器中运行 — 数据不会离开你的电脑。

常见问题

PostgreSQL 中 timestamp 和 timestamptz 有什么区别？

timestamp（不带时区）按原样存储日期时间值，没有任何时区上下文。timestamptz（带时区）在存储时将输入转换为 UTC，在检索时再转换回会话时区。几乎所有场景都应使用 timestamptz —— 它能防止分布式系统中与时区相关的 bug。

PostgreSQL 真的会在 timestamptz 中存储时区吗？

不会 —— 尽管名字如此，PostgreSQL 并不存储时区本身。它将输入转换为 UTC，仅存储 UTC 值（从 2000-01-01 起的微秒计数）。检索时，它根据会话的 timezone 设置从 UTC 转换为对应时区。原始时区信息会被丢弃。

如何更改 PostgreSQL 会话的时区？

执行 SET timezone = 'America/New_York'; 即可更改会话时区。这会影响 timestamptz 值的显示和解释方式。如需修改服务器级默认值，在 postgresql.conf 中设置 timezone。建议始终使用 IANA 时区名称（如 Asia/Shanghai）而非缩写（如 CST），以避免歧义。

存储事件时间应该用 timestamp 还是 timestamptz？

几乎所有场景都应使用 timestamptz —— 用户操作、API 调用、审计日志和定时任务。仅在存储与特定时刻无关的抽象时间时才使用 timestamp（不带时区），例如”商店 09:00 开门”表示当地时区的上午 9 点，而非某个具体的 UTC 时刻。

PostgreSQL 的 timestamptz 如何处理夏令时？

PostgreSQL 使用 timestamptz 时能正确处理夏令时，因为它内部以 UTC 存储所有数据。检索值时，PostgreSQL 根据会话时区的当前夏令时规则从 UTC 进行转换。这意味着同一个 UTC 时刻在夏令时切换前后会正确显示不同的本地时间。

想系统了解 Unix 时间戳，包括精度处理、时区最佳实践，以及 JavaScript、Python、Go 的代码示例，请阅读我们的 Unix 时间戳完全指南。

总结

• Postgres 的两种时间类型都是微秒计数器；标签才是唯一的区别
• 选错类型意味着令人困惑的时间戳和错误的计算
• 善用工具测试、转换和验证，可以节省数小时的调试时间