接触过不少用 PostgreSQL 做业务系统的团队，会看到一个很有意思的现象：别的功能大家都能查文档，一到时间函数就全靠临时搜，搜索关键词常年是postgresql 时间函数、postgresql extract、时间计算。这也不怪大家，PG 的时间体系确实比 MySQL 多一些概念，date、timestamp、timestamptz、interval、epoch随便拎出来一个都够绕半天。一旦没理清，后面全是坑。这篇文章我按自己这些年写 SQL 的习惯，把常用的时间获取、时间计算、时间提取和格式化一次性讲透，中间会把容易翻车的地方单独拎出来讲。示例基本基于 PostgreSQL 16，14 往上跑都没问题。
1. 先把时间类型搞对：date、timestamp、timestamptz 的存储和显示规则
很多人在时间函数上翻车，其实源头不是函数不会写，而是对数据类型理解得模模糊糊。PG 最常用的时间类型就这么几个，搞清楚它们的存储逻辑，后面再学函数就顺了。
1.1 三种类型怎么存、怎么显示
直接看表：
| 类型 | 是否带时区 | 存储逻辑 | 典型用途 |
|---|---|---|---|
date | 不带 | 存年月日，没有时分秒 | 生日、账单日期 |
timestamp | 不带 | 存墙上时间，你说几点就是几点 | 本地业务时间、纯记录 |
timestamptz | 带时区 | 内部统一转成 UTC 存储，显示时按会话时区换算 | 跨时区系统、订单时间、日志时间 |
最容易误解的是timestamptz：它并不是“在数据里存一个带时区的字符串”，而是先把你的输入时间换算成 UTC，显示的时候再根据当前会话的timezone转回本地时间。
SET timezone = 'UTC'; SELECT DATE '2024-06-01' AS d, TIMESTAMP '2024-06-01 12:00:00' AS ts, TIMESTAMPTZ '2024-06-01 12:00:00+08' AS tstz;上面tstz这一列，如果你把会话时区切到Asia/Shanghai，显示会变回2024-06-01 12:00:00+08；切到 UTC，就变成2024-06-01 04:00:00+00。存储的绝对时间点没有变，变的只是给人看的字符串。
这个特性是做跨时区系统的基石。反过来说，如果你用的是timestamp，那就完全不存在时区概念，你存的是哪个数，查出来还是哪个数，多一分“时区感”都没有。
1.2 为什么“字符串比较”会骗人
业务里经常见到有人拿 varchar 或者 text 存时间，然后用字符串直接比大小。
SELECT * FROM orders WHERE order_time_str >= '2024-06-01';这种写法在日期格式恰好是YYYY-MM-DD的时候能跑，但只要格式一乱就出事。比如有人存了2024/06/01 08:00:00，或者01-06-2024，字符串比较的结果就完全不是时间顺序了。更隐蔽的问题是，客户端和服务的系统 locale 不同，to_char、TO_DATE解析出来的月日顺序可能和你以为的完全相反。
所以我的建议很简单：表结构设计阶段就把时间列定义成正确的时间类型，而不是字符串。如果你已经在用字符串列了，也请在做比较之前显式转换：
SELECT * FROM orders WHERE (order_time_str::date) >= DATE '2024-06-01';这里先把字符串转成date，再比较。看起来多写一步，实际是在告诉数据库“请按时间语义比较”，而不是按字符顺序碰运气。
还有一个容易踩的隐式转换：timestamp和timestamptz直接比较时，PG 会按当前会话时区把无时区的时间解释一遍再去比。如果你没意识到这一点，同一段 SQL 在不同时区下跑出不同结果也正常。处理办法是统一列类型，或者全部显式转成同一类型。
2. 取“当前时间”到底该用哪个函数：now()、transaction_timestamp()、clock_timestamp() 的差异
很多刚接触 PG 的人以为“取当前时间就是now()”，然后写日志、写触发器、写定时任务全都用同一个。实际这几个函数在微妙的场景里差别非常大。
2.1 三个常用函数在一句话内的真实差异
先跑这句：
SELECT now(), transaction_timestamp(), current_timestamp, clock_timestamp();在同一个事务里执行，前三者基本一样，因为now()本质上就是transaction_timestamp()的别名，返回的是当前事务开始的那一刻。就算事务里执行了 10 分钟，你再调now()，它还是事务开始时刻的时间。
clock_timestamp()不一样，它返回的是这条语句真实执行到的当前时刻，每一次调用都可能变。
SELECT clock_timestamp(), pg_sleep(0.3), clock_timestamp();第二次调用会往后跳 0.3 秒左右。这在绝大多数场景里不是好事，但有些场景非常需要。
| 函数 | 语义 | 同一事务内多次调用 |
|---|---|---|
now() | 事务开始时刻 | 不变 |
transaction_timestamp() | 事务开始时刻 | 不变 |
current_timestamp | 事务开始时刻（SQL 标准） | 不变 |
clock_timestamp() | 真实当前时刻 | 每次都变 |
2.2 日志表、触发器、定时任务到底该选哪个
我自己的习惯是这样：
- 业务表的创建时间、更新时间，默认值一律用
now()或CURRENT_TIMESTAMP。好处是同一批事务里的数据时间戳一致，按事务维度去看业务状态时不会乱。 - 审计日志、触发器里记录“实际发生了什么”的场景，用
clock_timestamp()。比如一个BEFORE UPDATE触发器，事务可能持续很久，你希望记录的是每一行被修改的真实时刻，那now()就不够精确了。
举个例子：
CREATE TABLE order_audit ( order_id bigint, changed_at timestamptz DEFAULT clock_timestamp(), old_amount numeric, new_amount numeric ); CREATE OR REPLACE FUNCTION trg_order_audit() RETURNS trigger AS $$ BEGIN INSERT INTO order_audit(order_id, changed_at, old_amount, new_amount) VALUES (OLD.id, clock_timestamp(), OLD.amount, NEW.amount); RETURN NEW; END; $$ LANGUAGE plpgsql;还有statement_timestamp()这类函数，返回当前语句的开始时刻。逻辑上它介于now()和clock_timestamp()之间：一条 SQL 语句内部多次调用它，结果是稳定的，但不同语句之间会不同。这种适合在存储过程里对比“这条语句到底是几点跑的”。
最后提醒一句：current_timestamp是 SQL 标准写法，可以带精度，比如CURRENT_TIMESTAMP(3)，在需要毫秒位数稳定的场景很好用。不要小看这个括号，很多老 DBA 写习惯了也会漏。
3. 时间加减和差值计算：interval 的语法与 age() 的两个隐藏细节
时间加减是业务里最频繁的操作，比如“查最近 7 天订单”“算某个工单超时多久”“算用户年龄”。这里核心是interval和age()。
3.1 interval 写法和常用组合
interval看起来简单，但写法其实很灵活。
SELECT now() + INTERVAL '1 day'; SELECT now() - INTERVAL '2 hours 30 minutes'; SELECT TIMESTAMP '2024-01-01 08:00:00' + INTERVAL '1 day 2 hours';我见过不少同事只记INTERVAL '1 day'这一种，其实你完全可以把多个单位拼在一起。更规范的场景，尤其适合用make_interval来生成，尤其是单位放在变量里的时候：
SELECT now() + make_interval(days => 7, hours => 3);这个函数的好处是参数可读性好，代码审查的时候一眼能看懂“加了 7 天 3 小时”。
另外要强调：interval '1 month'不代表 30 天。它是按日历月走。比如 1 月 31 日加 1 个月，结果是 2 月 29 日而不是 3 月 2 日：
SELECT DATE '2024-01-31' + INTERVAL '1 month'; -- 结果：2024-02-29这一点在处理月底账单、订阅续费的时候尤其重要。如果业务希望“固定 30 天后”，那就应该写INTERVAL '30 days'，而不是INTERVAL '1 month'。
3.2 两个时间相减返回 interval，怎么取精确天数
timestamp - timestamp返回的是interval，不是数字：
SELECT TIMESTAMP '2024-03-01 12:00:00' - TIMESTAMP '2024-02-01 10:00:00'; -- 结果：29 days 02:00:00很多新手会拿EXTRACT(DAY FROM ...)去取天数，结果只拿到29。如果时间跨度超过一个月，这个字段还会变成月份加天数的组合，比如1 mon 5 days。这时候EXTRACT(DAY FROM ...)取到的只是5，就彻底跑偏了。
我的建议是：要精确计算两个时间点的差，先转成 epoch 秒，再除以 86400。
SELECT EXTRACT(EPOCH FROM ( TIMESTAMP '2024-03-01 12:00:00' - TIMESTAMP '2024-02-01 10:00:00' )) / 86400 AS diff_days; -- 结果：29.0833333333333333这样拿到的是带小数的实际天数。如果只需要整数天数，配合ROUND或者FLOOR处理即可。
3.3 age()：算“人间年龄”比直接减更像人话
age()是 PG 提供的专门算跨度的函数，它返回的不是总天数，而是“几年几月几天”这种人类更习惯的表示。
SELECT age(TIMESTAMP '2024-01-01', TIMESTAMP '2020-06-15'); -- 结果：3 years 6 mons 16 days两个参数分别是“终点”“起点”，不要写反。算用户年龄的时候用它特别方便：
SELECT age('2024-06-01'::date, '1995-08-20'::date);还有一种只传一个参数的写法，比如age(birthday)，默认以“今天午夜”为基准，而不是当前时刻。这会导致生日当天还没过零点时，年龄计算差一天。实际项目中我宁可写完整两个参数，或者用date_part('year', age(birthday))来提取周岁，不要偷懒省参数。
4. extract 提取年月日/周/季度/epoch：常用取值及边界条件
标题里被点名最多的就是extract。它干的事很纯粹：从一个时间值里把其中一个字段抽出来。
4.1 最常用的几种取值，尤其是 dow 和 isodow 的坑
直接看这个综合示例：
SELECT now(), EXTRACT(YEAR FROM now()) AS year, EXTRACT(MONTH FROM now()) AS month, EXTRACT(DAY FROM now()) AS day, EXTRACT(HOUR FROM now()) AS hour, EXTRACT(DOW FROM now()) AS dow, EXTRACT(ISODOW FROM now()) AS isodow, EXTRACT(WEEK FROM now()) AS week, EXTRACT(QUARTER FROM now()) AS quarter;DOW和ISODOW是最容易搞混的两个取值。DOW 从 0 开始，0 是周日，6 是周六；ISODOW 从 1 开始，1 是周一，7 是周日。如果你要按“周一是一周第一天”做统计，必须用 ISODOW，不能用 DOW。
比如统计每周一订单量：
SELECT EXTRACT(ISODOW FROM created_at) AS weekday, count(*) FROM orders WHERE created_at >= now() - INTERVAL '4 weeks' GROUP BY EXTRACT(ISODOW FROM created_at) ORDER BY weekday;另一个高频用法是EXTRACT(EPOCH FROM ...)，它把一个时间转成 Unix 时间戳秒数，兼容各种外部系统对接。
SELECT EXTRACT(EPOCH FROM now()); -- 秒级 Unix 时间戳，带小数注意EXTRACT返回的类型是numeric，如果项目里对接 Java 的long或者 Go 的int64，记得在应用层转一下整数。
EXTRACT(WEEK FROM ...)也有坑：PG 的周算法是按照 ISO 8601 来的，即周一作为一周开始，且一年第一周必须包含当年第一个星期四。所以 12 月 31 日查询 week 可能变成下一年的第 1 周，这种边界在报表里要提前想清楚。
4.2 extract 和 date_part 的关系：别被老函数带偏
PG 里还有一个老函数叫date_part，传参方式不同：
SELECT date_part('year', now()); SELECT extract(year FROM now());功能几乎一样，但date_part返回的是double precision，而extract返回numeric。在需要精确到小数秒再参与四舍五入的场景里，这个类型差异可能会产生细微偏差。
我个人建议新代码统一用extract，可读性更好，也更接近 SQL 标准。date_part看老项目的时候认识就行，不用再教给别人。
extract也可以用在interval上，比如取出间隔里的小时数：
SELECT EXTRACT(HOUR FROM INTERVAL '2 days 5 hours'); -- 结果：5，而不是 53这里很多人会意外：为什么不是总小时数？因为extract只取字段本身，不做单位换算。想要总小时数，还是要先转 epoch，或者直接对 interval 做运算。
5. 按粒度对齐和格式化：date_trunc、to_char 的组合用法
extract是把时间拆成单个字段，但实际统计需求往往是“按小时分组”“按天分组”“按周分组”。这时候用date_trunc比extract更顺手。
5.1 date_trunc 做分组统计：按天、按小时、按周对齐
date_trunc的作用是把一个时间“截断”到指定精度，类似把一堆乱数归整到刻度上。
SELECT date_trunc('day', now()); -- 得到当天 00:00:00它的单位可以传hour、day、week、month、quarter、year等。我最常用的是按小时和按天统计：
SELECT date_trunc('hour', created_at) AS hour_bucket, count(*) AS order_count FROM orders WHERE created_at >= now() - INTERVAL '24 hours' GROUP BY date_trunc('hour', created_at) ORDER BY hour_bucket;这段 SQL 会把过去 24 小时的订单切成 24 个桶，每小时一个，统计非常直观。
按周统计时有个细节：PG 的date_trunc('week', ...)也是以周一作为一周起点，和ISODOW的逻辑一致。所以团队里定“周一是一周第一天”的话，整个链路都是统一的，不会出现某一天被归到上周末的尴尬。
如果你用的 PG 版本较新，date_trunc还支持quarter，直接按季度分组做报表很省事。老版本也不慌，可以用to_char(created_at, 'YYYY') || '-Q' || EXTRACT(QUARTER FROM created_at)拼出季度文本。
5.2 to_char 模板串：想要什么格式就有什么格式
to_char就是 PG 里的格式化函数，和 Excel 自定义格式有点像。
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS'); -- 2024-06-01 14:30:45注意这里HH24是 24 小时制。如果你只写HH，PG 默认是 12 小时制，下午 2 点多会被显示成 02 而不是 14，很多报表错就错在这。
如果只需要日期部分：
SELECT to_char(now(), 'YYYY-MM-DD');还需要毫秒的时候：
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS.MS');MS是毫秒，US是微秒，别记反了。
to_char还经常和date_trunc配合用，一个负责对齐，一个负责展示：
SELECT to_char(date_trunc('day', created_at), 'YYYY-MM-DD') AS day, count(*) FROM orders GROUP BY date_trunc('day', created_at) ORDER BY day;6. 时区、索引失效这些坑，真的会吃掉半天时间
时间函数本身不难，难的是和时区、索引、执行计划纠缠在一起。这里说三个我实际遇到过的问题。
6.1 timestamptz 的时区显示：改 session 时区后的诡异现象
接手的线上库默认在 UTC 时区跑，业务方在中国，报表想显示北京时间，于是有人直接在查询前SET timezone = 'Asia/Shanghai'，看起来没问题。
但如果你把timestamptz类型存进去，再用to_char格式化，那to_char会按当前会话时区输出，出来的就是北京时间，没毛病。麻烦的是，如果你用的是timestamp类型，SET timezone对它的显示完全不起作用，它永远是存进去的那个值。于是同一套代码，换个环境结果表现就不一样。
我的经验是：只要这个时间点可能在多个时区被解释，一律用timestamptz。如果想要“本地时刻”做分组统计，也先转成timestamptz再按业务时区去AT TIME ZONE，不要拿裸timestamp硬算。
常用的转换写法：
SELECT now() AT TIME ZONE 'Asia/Shanghai'; -- 返回 timestamp，是北京时间对应的本地墙上时间6.2 函数套在列上，索引失效是必然的
这是老生常谈，但每天都在发生。比如想在某个时间列上查某一天的数据，第一反应是写：
SELECT * FROM orders WHERE date_trunc('day', created_at) = DATE '2024-06-01';如果你在created_at上建了普通 B-tree 索引，这条 SQL 基本走不上。因为date_trunc('day', created_at)是对列做完函数运算后的结果，普通索引里存的原始值没法直接匹配。
处理办法有两个。
第一个，也是最推荐的：把条件改成范围查询。
SELECT * FROM orders WHERE created_at >= TIMESTAMPTZ '2024-06-01 00:00:00+08' AND created_at < TIMESTAMPTZ '2024-06-02 00:00:00+08';这样既能走索引，语义也更清晰。
第二个，如果你确实每天都要按“日粒度”查询，建表达式索引：
CREATE INDEX idx_orders_created_day ON orders (date_trunc('day', created_at));但表达式索引有维护成本，而且会让执行计划选择变复杂。大多数业务场景下，范围查询是更稳妥的方案。
6.3 一个排查案例：为什么同一条 SQL 在测试库快、生产库慢
有次排查慢查询，发现测试环境WHERE created_at >= '2024-06-01'秒回，生产环境却扫全表。看执行计划发现，created_at在测试库是timestamptz，生产库不知道为什么被定义成了varchar。查询条件里给了字符串，PG 就想把整列做隐式转换，索引自然废了。
把生产库的列改成timestamptz并重新建索引后，问题立刻消失。这件事给我留下的教训是：在 PG 里，时间函数能不能用舒服，50% 取决于表结构设计得对不对。类型对了，后面的extract、date_trunc才会听你的话。
我个人现在写任何日期查询，第一件事不是去想用什么函数，而是先问自己：这个条件是落在存储字段上，还是落在结果展示上？落在存储字段上，就老老实实写成范围条件，让索引说话；落在展示上，再交给to_char、date_trunc这些函数去处理。把这两件事分开，PG 的时间函数用起来会顺手非常多。
最后顺手分享一个小技巧：如果你频繁需要在 SQL 里输出“当前时间 + 偏移”，写多了容易眼花，可以把常用偏移先定义成 CTE 或者变量，比如WITH params AS (SELECT now() - INTERVAL '7 days' AS since) SELECT ... WHERE created_at >= (SELECT since FROM params);这样 SQL 逻辑一眼就能看明白，后期维护也省心。
