跳到主要内容

日期/时间类型

PostgreSQL 的日期/时间类型分别表达“日历日期”“一天中的时间”“不带时区的日期时间”“带时区语义的时间点”和“时间间隔”。最重要的设计原则是：先判断字段表示时间点还是日历概念，再选择类型。

常用类型​

类型表达的含义典型场景
date年、月、日生日、账期、节假日
time一天中的时刻营业时间、闹钟时间
timestamp不带时区的日期和时间墙上时钟读数、当地营业时间
timestamptz绝对时间点，按会话时区显示创建时间、事件发生时间
interval时间长度或日历间隔“3 天后”“1 个月”

PostgreSQL 中 timestamp with time zone 常写作 timestamptz。它不会保存原始时区名称，只保存可换算为 UTC 的时间点；查询时按 TimeZone 会话参数显示。

时间点与时区​

SET TIME ZONE 'Asia/Shanghai';

SELECT '2026-09-21 10:00+08'::timestamptz;
SELECT now(), current_timestamp;

同一个 timestamptz 值切换会话时区后显示不同，但时间点不变：

SET TIME ZONE 'UTC';
SELECT event_at FROM events;

SET TIME ZONE 'America/Los_Angeles';
SELECT event_at FROM events;

服务端和应用应约定时区策略。常见做法是数据库保存 timestamptz，接口统一输出带偏移的 ISO 8601 字符串，展示层再转换为用户时区。

timestamp 不是“没有时区的 UTC”​

timestamp without time zone 只保存字段中的年月日时分秒，不会记录它属于哪个时区：

SELECT '2026-09-21 10:00+08'::timestamp;

输入中的 +08 会被忽略。若值实际表示一个跨地区发生的事件，应使用 timestamptz；只有“每天 10 点营业”这类不绑定绝对时间点的概念才适合 timestamp 或 time。

精度、日期和时间运算​

类型可以带精度 p，表示小数秒位数，例如 timestamp(3) 保存到毫秒。精度应根据来源和业务需要设置，不要以为更高精度一定更准确。

SELECT date '2026-09-21' + interval '2 months';
SELECT timestamptz '2026-09-21 09:00+08' + interval '90 minutes';
SELECT finished_at - started_at AS elapsed FROM jobs;

interval '1 month' 是日历月份，不等于固定天数；月末加减可能出现不同日期。需要固定时长时使用小时、分钟或秒，并用目标时区做边界测试。

夏令时与截断​

date_trunc 可以按指定粒度截断时间：

SELECT date_trunc('day', event_at, 'Asia/Shanghai')
FROM events;

跨夏令时地区时，“一天”不一定是 24 小时。统计按自然日分组时要明确使用哪个时区；统计过去 24 小时时应使用时间点差值，而不是本地日期减法。

默认值、索引和范围查询​

CREATE TABLE events (
  event_id bigint PRIMARY KEY,
  event_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX events_event_at_idx ON events (event_at);

范围查询使用半开区间通常更稳健：

WHERE event_at >= $1
  AND event_at < $2

不要把列包在 to_char、date() 等函数中再过滤，否则普通索引可能无法使用；需要按日期搜索时，传入边界时间或建立匹配的表达式索引。

常见误区​

  • 把时间点保存为文本，导致排序、校验和时区换算不可靠。
  • 把 timestamp 当成 UTC 保存，却没有记录约定，之后无法恢复真实时间点。
  • 用 now() 的字符串结果拼 SQL，而不是使用参数化时间值。
  • 以为 interval '1 month' 永远等于 30 天。
  • 忽略夏令时、闰秒处理和数据库会话 TimeZone 设置。