42. 日期时间函数与操作符

本节目标：掌握 now()、current_date、date_trunc、interval 运算、extract 和 age 等日期时间处理技巧。

获取当前时间

now() / current_timestamp：当前事务开始时刻的时间戳（带时区）。
current_date：今天的日期。
current_time：当前时间（带时区）。

postgres=# SELECT now(), current_date, current_time;

Note

同一事务内，now() 返回值固定不变。想拿”真正此刻”的时间用 clock_timestamp()，但它不适合做默认值，因为它在一条语句里每行都可能不同。

建表示例里常用 DEFAULT now() 自动记时间：

postgres=# CREATE TABLE orders (
postgres=#   id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
postgres=#   user_id    INT,
postgres=#   amount     NUMERIC(10,2),
postgres=#   status     TEXT,
postgres=#   created_at TIMESTAMP DEFAULT now()
postgres=# );

时区与 timestamptz

PostgreSQL 有两种时间类型：timestamp（不带时区）和 timestamptz（带时区，全称 timestamp with time zone）。timestamptz 存的是 UTC 时刻，展示时会按会话时区转换。

postgres=# SET TIME ZONE 'Asia/Shanghai';
postgres=# SELECT now()::timestamptz;
postgres=# SET TIME ZONE 'UTC';
postgres=# SELECT now()::timestamptz;  -- 同一时刻，显示不同

Warning

业务系统建议统一用 timestamptz，别用裸 timestamp。裸 timestamp 不会记录时区，跨时区部署时极易算错”几点”。

日期时间运算

时间戳 / 日期可以和 interval（时间间隔）相加减：

postgres=# SELECT now() + interval '1 day';     -- 明天此刻
postgres=# SELECT now() - interval '2 hours';   -- 两小时前
postgres=# SELECT date '2026-07-31' + 7;        -- 加7天
postgres=# SELECT date '2026-08-01' - date '2026-07-25';  -- 相差天数 7

interval 也能自己做乘法：

postgres=# SELECT 3 * interval '1 hour';  -- 03:00:00

两个日期相减得到天数（整数），两个 timestamp 相减得到 interval：

postgres=# SELECT age(timestamp '2026-07-31', timestamp '2026-07-25');  -- 6 days

date_trunc 截断精度

date_trunc 把时间截到指定精度，比如截到”天”得到当天零点：

postgres=# SELECT date_trunc('day', now());
postgres=# SELECT date_trunc('month', now());
postgres=# SELECT date_trunc('hour', now());

常用来按天、按月做分组统计：

postgres=# SELECT date_trunc('day', created_at) AS day, COUNT(*)
postgres=# FROM orders
postgres=# GROUP BY day
postgres=# ORDER BY day;

extract 提取字段

extract 从一个日期里抠出年、月、日、小时等字段：

postgres=# SELECT extract(year from now());    -- 2026
postgres=# SELECT extract(month from now());  -- 7
postgres=# SELECT extract(dow from now());    -- 星期几(0=周日)
postgres=# SELECT extract(epoch from now());  -- 距 1970-01-01 的秒数

date_part 和 extract 等价，只是写法不同：

postgres=# SELECT date_part('hour', now());

age 计算年龄 / 时长

age 计算两个时间之间”带年月”的差值，比单纯减天数更贴近日常理解：

postgres=# SELECT age(date '2000-01-01');                  -- 距今天数折算成 年月日
postgres=# SELECT age(timestamp '2026-07-31', timestamp '2000-01-01');

算用户注册时长常用：

postgres=# SELECT username, age(created_at) FROM users;

Tip

age 返回 interval，包含”x 年 x 月 x 日”，比 now() - created_at 的纯天数更直观。它接收时间类型，users 表里的 created_at 正好能直接喂给它，不用另加列。

格式化输出 to_char

to_char 把日期按模板格式化成字符串，做报表展示很常用：

postgres=# SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS');
postgres=# SELECT to_char(now(), 'YYYY年MM月DD日 Day');

常用模板：YYYY 年、 MM 月、 DD 日、 HH24 24 小时制、 MI 分、 SS 秒、 Day 星期名。

构造日期与时间

手动拼一个日期 / 时间：

postgres=# SELECT make_date(2026, 7, 31);
postgres=# SELECT make_timestamp(2026, 7, 31, 23, 59, 0);

把”秒数”转回时间（和 extract(epoch) 相反）：

postgres=# SELECT to_timestamp(1753900000);

时间段是否重叠

用 OVERLAPS 判断两个时间段有没有交集：

postgres=# SELECT (date '2026-07-01', date '2026-07-15')
postgres=#        OVERLAPS
postgres=#        (date '2026-07-10', date '2026-07-20');  -- t（有重叠）

AT TIME ZONE 时区转换

要把一个 timestamptz 转成另一个时区的时间，用 AT TIME ZONE：

postgres=# SELECT now() AT TIME ZONE 'Asia/Shanghai';  -- 上海时间
postgres=# SELECT now() AT TIME ZONE 'UTC';            -- UTC 时间

Note

AT TIME ZONE 作用在 timestamptz 上返回 timestamp（不带时区）；作用在 timestamp 上返回 timestamptz。方向是反的，别搞混。

间隔的规范化：justify

justify_days / justify_hours 把”超长的间隔”折成更自然的单位，比如 40 天变成”1 个月 10 天”：

postgres=# SELECT justify_days(interval '40 days');    -- 1 mon 10 days
postgres=# SELECT justify_hours(interval '30 hours');  -- 1 day 06:00:00
postgres=# SELECT justify_interval(interval '400 days');

构造时间与间隔

除了 make_date，还能构造具体时间和间隔：

postgres=# SELECT make_time(23, 59, 0);                 -- 23:59:00
postgres=# SELECT make_interval(days => 10, hours => 3);-- 10 days 03:00:00

判断有限时间：isfinite

某些特殊值（如 infinity）不是真正的时间点。用 isfinite 判断是否为有限时间：

postgres=# SELECT isfinite(timestamp 'infinity');  -- f
postgres=# SELECT isfinite(now());                -- t

示例：算两个日期相差的几个月

简单相减得到天数，要”相差几个月”得用 age 或 EXTRACT 组合：

postgres=# SELECT
postgres=#   EXTRACT(year  FROM age(d2, d1)) * 12 +
postgres=#   EXTRACT(month FROM age(d2, d1)) AS months
postgres=# FROM (VALUES (date '2026-01-15', date '2026-07-31')) AS t(d1, d2);

Tip

别直接用 EXTRACT(epoch ...) / 30 算月份，平月大月不一样，会得到错的”月数”。用 age 最稳。

时区到底该怎么存

前面强调过业务系统用 timestamptz。这里再展开一句：存进去的是 UTC 时刻，查出来按会话时区显示。所以同一时刻，上海用户看到的是白天，伦敦用户看到的是凌晨，但底层存的是同一个值，不会乱。千万别为了”好看”把时间存成字符串或裸 timestamp，后期做跨时区统计会哭。

日期计算的高频需求

实际写业务，日期计算有几类最常见：算两个日期差几天（date1 - date2）、算 N 天后是几号（date + n）、截到某精度做分组（date_trunc）、算年龄（age）。这几样练熟，八成的时间处理需求都能覆盖。剩下冷门的，现查文档也不迟。

别踩这些隐性错误

第一，拿字符串直接加减日期会报错，记得先转 date 类型。第二，age 算的是 interval，想拿整数岁要用 EXTRACT(year FROM age(...))。第三，算”相差几个月”别除以 30，月份天数不固定。这些坑我基本都踩过。

时间的存储精度选择

timestamp / timestamptz 默认精确到微秒，足够绝大多数业务。如果需要更高精度或只关心日期，用 date 最省空间也最清晰。别图省事全用 text 存时间——那样既不能比较大小，也不能做日期运算，后患无穷。

一个常见需求：本月第一天

报表常要”本月第一天零点”。用 date_trunc('month', now()) 一步到位：

postgres=# SELECT date_trunc('month', now()) AS month_start;

同理，本周一、本年初都能用 date_trunc('week', now())、 date_trunc('year', now()) 拿到。这套写法记住，能省很多手写日期拼接的麻烦。

时间戳与秒数互转

有时系统之间要对齐时间，会用到”Unix 时间戳”（距 1970-01-01 的秒数）。extract(epoch from ...) 把时间转成秒数，to_timestamp(...) 再转回来：

postgres=# SELECT extract(epoch from now());
postgres=# SELECT to_timestamp(1753900000);

Tip

不同语言的时间戳单位可能不同：有些是秒，有些是毫秒。从外部拿到的毫秒时间戳记得先除以 1000 再 to_timestamp，否则时间会跑到几万年以后。

Tip

想筛选「某天内」的数据，别用 BETWEEN 当天 AND 当天 + interval '1 day'——那样会把明天零点也包进去。正确写法是用半开区间：created_at >= 当天 AND created_at < 明天零点。或者先 date_trunc('day', created_at) 截到天再等于某天。我见过有人写 BETWEEN '2026-07-31' AND '2026-07-31 23:59:59'，结果漏掉 23:59:59 之后不到一秒的数据，账目就对不上了。半开区间 [当天, 明天) 才是处理时间段的黄金写法，记住它最稳，别再手写 23:59:59 这种容易漏边的写法。

常见误区

用 timestamp 而不是 timestamptz，跨时区部署算错时间。
以为 now() 在一条语句里每次都变。不会，它绑定事务开始时刻。
用字符串直接加减日期，比如 '2026-07-31' + 1 会报错，要先转成 date 类型。
extract(dow) 里 0 代表周日，不是周一，做”周几”判断时别搞反。
age 算出来是 interval，想拿”整数岁”得自己再 extract(year from age(...))。