13. 日期时间类型

时间相关的数据很常见：用户注册时间、订单创建时间、优惠券有效期。PostgreSQL 提供了一组类型来精确表达「哪年哪月哪日几点」。这里最容易被坑的就是时区，我见过不少系统因为时区用错，凌晨下单的订单跨天对不上。下面把类型讲清楚。

五种基本类型

类型	存什么	占空间
date	年月日，不含时刻	4 字节
time	一天里的时刻，不含日期	8 字节
timestamp	日期 + 时刻，无时区	8 字节
timestamptz	日期 + 时刻，带时区	8 字节
interval	时间间隔，如「3 天 5 小时」	16 字节

还有 time with time zone（timetz），但用得很少，一般不建议碰。

时区是重点

timestamp 不存时区信息。你写进去是什么，取出来就是什么，换了时区设置它也不会变。它只是「墙上时钟的字面值」。

timestamptz（全称 timestamp with time zone）是「带时区」的。它内部一律按 UTC 存储，显示时再按当前会话时区转成本地时间。也就是说，同一个时刻，不同时区的人看到的是各自的本地时间，但底层存的是同一瞬间。

SET timezone = 'America/New_York';
SELECT now();
-- 2026-07-31 10:00:00-04

SET timezone = 'Asia/Shanghai';
SELECT now();
-- 2026-07-31 22:00:00+08  （同一时刻，不同时区显示不同）

取当前时间

最常用的是 now()，返回带时区的当前时刻：

SELECT now();
SELECT CURRENT_DATE;          -- 只要日期，如 2026-07-31
SELECT CURRENT_TIME;          -- 只要时刻，带时区
SELECT now()::date;           -- 截取日期部分
SELECT now()::time;           -- 截取时刻部分

还有 transaction_timestamp()（等价于 now()）、statement_timestamp()，以及 clock_timestamp()（每次调用都取实时，同一语句里多次调用值不同）。入门阶段用 now() 就够了。

常用时间函数

extract 从日期里取出某个字段，比如年、月、日、小时：

SELECT extract(year from now());     -- 2026
SELECT extract(dow from now());     -- 星期几，0=周日
SELECT extract(hour from now());    -- 当前小时

age 算两个日期之间相差的年月日，常算「年龄」「使用年限」：

SELECT age(DATE '2000-05-01');            -- 距今天数折算成 年 月 日
SELECT age(DATE '2026-07-31', DATE '2020-01-01');  -- 相差区间

to_char 把时间格式化成任意字符串，做报表展示很好用：

SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS');  -- 24小时制
SELECT to_char(now(), 'YYYY年MM月DD日');          -- 中文格式

自动时间戳

建表时用 DEFAULT now()，插入时不填该列就会自动写入当前时间。我们的 orders 表就是这么做的：

CREATE TABLE orders (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id INT,
  amount NUMERIC(10,2),
  status VARCHAR(20),
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO orders (user_id, amount) VALUES (1, 99.90);
SELECT * FROM orders;

这样每行创建时间都不用手动维护。如果想记录「最后更新时间」，可以用触发器或应用层更新，本书第 45 章会讲触发器。

时间段 interval

interval 表示一段间隔，常做日期运算：

SELECT now() + INTERVAL '7 days';     -- 7 天后的此刻
SELECT now() - INTERVAL '1 month';    -- 1 个月前
SELECT now() + INTERVAL '2 hours 30 minutes';

也可以直接对 date 加天数：

SELECT DATE '2026-07-31' + 10;        -- 2026-08-10

两个高频时间函数

除了 now()，还有两个函数几乎天天用。age(timestamp) 算「距离现在多大」，常用于年龄、账龄；date_trunc('month', now()) 把时间截断到某粒度，比如「本月初」，做按月统计的分组键很方便。

SELECT age(DATE '2000-05-01');                    -- 距今天数折算
SELECT date_trunc('month', now()) AS 本月第一天;

记住 date_trunc 不是「四舍五入」，而是「向下截断」：截到小时就丢掉分钟秒，截到月就丢掉日及以后。做区间统计时这个特性很好用，能轻松算出「每一天的零点」「每一月的第一天」当作分组起点。

为什么时间类型容易出错

时间类型是新人出错最多的地方，根源几乎都在「时区」。很多人直觉上以为数据库会像人一样「记住这是北京时间」，其实 timestamp 只是把字面值存进去，不带任何时区信息；不同时区的会话读出来都不变，于是「凌晨下的单」在另一时区看成了「前一天晚上」，统计就乱了。

另一个坑是「只存日期却要算时长」。date 没有时刻，两个 date 相减得到的是「天数」这个整数，不是带时分秒的 interval。要算精确到秒的间隔，字段必须是 timestamp/timestamptz。

把这两点记牢——涉及时区用 timestamptz、算间隔用带时刻的类型——时间相关的 bug 能少一大半。

时区转换实战

timestamptz 存 UTC，显示时按会话时区转。想看某个固定时区的样子，用 AT TIME ZONE：

SET timezone = 'Asia/Shanghai';
SELECT now();                         -- 上海时间
SELECT now() AT TIME ZONE 'UTC';      -- 强制看成 UTC
SELECT now() AT TIME ZONE 'America/New_York';  -- 看成纽约时间

注意 AT TIME ZONE 返回的是 timestamp（无时区），因为它已经「落地」成那个时区的字面值了。

extract 与 date_part

extract 从日期里抠出某个部分，等价函数叫 date_part：

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

epoch 在做时间差、算时长时很有用，比如两个时间戳相减得到 interval，再 extract(epoch from ...) 得到秒数。

interval 的更多玩法

interval 能做加减，也能取部分：

SELECT INTERVAL '2h 30m' + INTERVAL '1h';   -- 3 小时 30 分
SELECT justify_hours(INTERVAL '25h');        -- 规整成 1 天 1 小时

判断「最近 7 天注册的用户」的写法：

SELECT * FROM users WHERE created_at >= now() - INTERVAL '7 days';

常见时区陷阱

新人最容易踩的坑：把 created_at 定义成 timestamp（无时区），又按本地时间写入，结果换时区查询时全错位。还有人以为 now() 返回的是会话时区字符串、存进 timestamp 会丢信息——确实会。统一用 timestamptz 能从根上避开这些问题。

常见写法与转换

输入日期时间字面量时，推荐带类型转换，避免格式歧义：

SELECT DATE '2026-07-31';
SELECT TIMESTAMP '2026-07-31 14:30:00';
SELECT TIMESTAMPTZ '2026-07-31 14:30:00+08';

date 和 timestamp 之间可以直接转，timestamp 转 date 会丢掉时刻部分。想换成不同时区显示，用 AT TIME ZONE：

SELECT now() AT TIME ZONE 'Asia/Tokyo';