PostgreSQL时间函数全解析：从类型、计算到性能优化实战

做后端开发这几年，我发现自己跟 PostgreSQL 打交道最多的，除了增删改查，就是跟时间相关的各种函数。不管是出报表、做统计分析、算用户活跃度，还是处理日志表、判断订阅到期时间，几乎每个业务场景都绕不开时间处理。PostgreSQL 的时间函数之所以值得单独拿出来写一篇，是因为它数量多、用法灵活，但坑也多，尤其是刚接触 PG 的同事，经常把 MySQL 或 SQL Server 的习惯带过来，结果算出来的结果对不上。这篇内容我会把平时用得最多的时间函数、时间计算和提取手法做一个完整梳理，每个函数都带你过一遍真实场景，也会把我踩过的坑标注出来，希望能帮你少走点弯路。

1. 时间函数体系：先搞懂类型，再谈函数

1.1 数据类型选不对，后面全是坑

很多人在 PG 里写时间相关的 SQL，第一步就选错了类型。PostgreSQL 的时间类型主要有date、time、timestamp without time zone、timestamp with time zone（简称timestamptz）、interval这几种。单看名字好像能猜个八九不离十，但实际用起来差别非常大。

我自己的经验是：业务系统里存“某个时刻”这种语义的数据，一律用timestamptz，不要用timestamp without time zone。举个例子，用户下单时间、日志上报时间、支付完成时间，这些都是“绝对时刻”，你用timestamptz存，数据库内部是按 UTC 存储的，展示的时候根据会话的时区设置自动转换成当地时间。这样无论你的服务器在哪个地区、客户端在哪个城市，算出来的都是同一个物理时刻，不会出现“同一个订单在两台机器上看到的支付时间差了8小时”这种事。

而timestamp without time zone存的是“墙上时钟时间”，不带任何时区信息，适合用来表示日程表、节假日这种与位置无关的日历时间。比如你在系统里配置“2025-06-01 09:00:00 发布活动”，这个时间应该是一个纯粹的本地时间，用timestamp就很合适，因为它不随查看者所在地区变化。

interval是一个相对时间，表示一段时间跨度，比如interval '2 hours'、interval '3 days 4 hours 5 minutes'。我在业务里经常用它来做时间加减，比如created_at + interval '7 days'就是在创建时间上往后推一周。理解了三种时间语意（绝对时刻、本地时刻、时间跨度），后面的函数才好理解。

1.2 内置时间函数一图流：current、now、clock 家族的差别

PG 里获取当前时间的函数有好几个，这是我新人时期最早搞混的一批函数：

我记得有一次排查一个数据问题，系统里有个字段存的是“数据生成时间”，用的是now()，但因为一个长事务里跑了十几分钟的批量任务，导致这一批数据生成时间全部记成了事务开始的时间，而不是每条数据真正写入的时间。如果你在循环里执行插入，希望每条数据都拿到“当下”的时间，就要用clock_timestamp()而不是now()。

这个特性的基本原理是：now()在事务开始时固定下来，是为了保证事务内多次调用返回同一个时间戳，维系逻辑一致性；而clock_timestamp()每次调用取的是数据库服务器当前的系统时间，会真实流逝。大部分业务场景需要“一单一个时间”，更推荐在应用层生成时间或在插入时用clock_timestamp()，特别要注意使用批量插入、长事务的场景。

1.3 三个高频使用场景：时间越界、默认值、过滤条件

时间函数最基础也最常用的地方有三个：定义表字段默认值、写入数据时标记时间、查询时做时间范围过滤。

定义默认值我建议直接用now()或current_timestamp，不要用clock_timestamp()。因为默认值是一个 DDL 层面的东西，用事务稳定的时间更符合逻辑，而且now()是 stable 函数，PostgreSQL 在生成执行计划时会做优化，而clock_timestamp()是 volatile 函数，会阻止某些执行计划优化，在字段默认值这种场景没有好处。

过滤条件比如“查最近7天的订单”，最常见的正确写法是：

SELECT * FROM orders WHERE created_at >= now() - interval '7 days';

这里now()获取的是事务开始时间作为基准，在长事务里可能会有偏差。如果业务要求的是“以当前时刻为准”，可以用clock_timestamp()，但要注意这在索引扫描时有所影响，PostgreSQL 无法用连续范围优化，因为clock_timestamp()每次扫描返回的值可能不同。普通报表、接口查询，now()完全够用，别没事就上clock_timestamp()。

2. 时间计算：从加减interval到求两个日期的精确差值

2.1 日期加减法：interval 是万能钥匙

PostgreSQL 里时间加减的核心就是interval，它可以直接和date、timestamp、timestamptz做运算：

-- 往后推30天 SELECT date '2025-01-01' + interval '30 days'; -- 往前推2小时 SELECT now() - interval '2 hours'; -- 组合单位 SELECT now() + interval '1 day 2 hours 3 minutes';

还有几个构造 interval 的函数，很好用：

• make_interval(days => 10, hours => 2)：按参数构造 interval，适合传入变量拼参数
• justify_days(interval '30 days')：把 30 天转成 1 mon 0 days
• to_char(interval, 'HH24:MI')：格式化 interval 的输出

我看到不少新人会这样写“去年同日”：SELECT now() - interval '1 year'。这在绝大多数情况下没问题，但要注意 2 月 29 日这种特殊日期，比如今年的 2 月 29 日减去一年，PG 会返回 2 月 28 日，因为目标月份没有 29 日。这个行为是 PG 默认的钳制规则，实际业务里需要确认是否符合预期。

interval可以理解为一种“带方向的持续时长”，它跟date相加的运算语义很直观。如果你需要加“整数天”，除了interval '7 days'，也可以直接date + integer，比如current_date + 7，这等价于往后推 7 天，只适用于date类型的运算，对timestamp类型不适用。

2.2 计算两个时间之差：age 和直接相减的区别

很多人以为两个日期相减在 PG 里也会像 MySQL 那样返回一个数字，但 PG 的行为不同。先看一段：

SELECT date '2025-01-10' - date '2025-01-01'; -- 返回 9（整数天，因为date相减返回整数） SELECT timestamp '2025-01-10 12:00:00' - timestamp '2025-01-01 08:00:00'; -- 返回 interval '9 days 04:00:00'

所以：date和date相减，返回的是整数天数；timestamp和timestamp相减，返回的是一个interval。这是很常见的一个坑，很多从 MySQL 转过来的同事会以为 PG 也返回数字，结果代码里直接拿来做除法，逻辑直接乱了。

如果你想把timestamp相减得到的小时数、分钟数算出来，推荐用EXTRACT(EPOCH FROM interval)。EPOCH 是时间戳纪元秒数，对 interval 类型来说就是这段间隔的总秒数：

SELECT EXTRACT(EPOCH FROM (timestamp '2025-01-10 12:00:00' - timestamp '2025-01-01 08:00:00')) / 3600 AS hours_diff; -- 结果为 100.0 小时

另一个常用的函数是age()。它专门用来计算年龄（或经过的时间跨度），返回的是带年月日的 interval：

SELECT age(timestamp '2015-06-01', timestamp '2025-01-01'); -- 返回 9 years 7 mons 0 days -- 只有一个参数时，默认为当前时间（事务开始时间） SELECT age(timestamp '2015-06-01'); -- 返回从2015年至今的时间间隔

age()与直接相减的差异在于展示方式：直接相减返回精确到天/小时/分钟的 interval，age()按月、日分段展示。做“用户年龄”“会员时长”这类统计时，age()是首选。

2.3 从 interval 中提取秒、分钟、小时：两种方式都掌握

前面讲到EXTRACT(EPOCH FROM interval)能拿到总秒数，那如何从 interval 里拿到“小时部分”或“分钟部分”呢？我平时用两种写法：

-- 方法一：用 extract SELECT EXTRACT(HOUR FROM interval '2 days 3 hours 40 minutes') AS hour_part, -- 3 EXTRACT(MINUTE FROM interval '2 days 3 hours 40 minutes') AS minute_part; -- 40 -- 方法二：用 date_part SELECT date_part('hour', interval '2 days 3 hours 40 minutes'), date_part('minute', interval '2 days 3 hours 40 minutes');

要注意的是，这里提取的hour_part不是“总小时数”，而是“去掉整天后的余额小时数”。interval '2 days 3 hours 40 minutes'的总小时数应为2*24 + 3 = 51，但EXTRACT(HOUR ...)只返回 3。这个区别在算总秒数和按单位取整时很容易搞混，建议统一用 EPOCH 拿总秒数，再自己换算成小时、分钟，逻辑更清晰，不容易踩坑。

2.4 日期边界：月初、月末、季度初、上周一

报表开发里关于“周期边界”的需求非常多。比如月报要看月初到现在，周报要看周一到今天，季度报表要看本季度初到现在。PG 的date_trunc()是处理这类问题的大杀器：

-- 当月1号 00:00 SELECT date_trunc('month', now()); -- 当周周一 00:00（按PG默认的周起始） SELECT date_trunc('week', now()); -- 当天0点 SELECT date_trunc('day', now()); -- 当前季度第一天 SELECT date_trunc('quarter', now());

date_trunc的语义很直观，就是“按指定精度截断到该周期的起点”，它返回的类型与输入一致，所以可以直接拿来和timestamptz列比较。注意 PG 的date_trunc('week', ...)默认一周从周一开始，与某些国家习惯周日开始不同。这里如果业务上要求“周日为一周起始”，必须自己做偏移，别直接拿week截断就以为完事了。

关于上个月末、上周日这类“终点边界”，我常用的技巧是先截断到今天，再减一个 interval：

-- 上月末 23:59:59.999 SELECT date_trunc('month', now()) - interval '1 microsecond'; -- 上周日 23:59:59.999 SELECT date_trunc('week', now()) - interval '1 microsecond';

这类“闭区间”写法在查询中非常常见。我更推荐的业务写法是只用开区间和闭区间组合，比如查询某月的数据写成created_at >= date_trunc('month', now()) AND created_at < date_trunc('month', now()) + interval '1 month'，这个后面在第 5 章展开讲。

3. 时间提取：extract、date_part、to_char 的三板斧

3.1 extract 提取年月日时分秒

如果要从一个时间戳里单独取出年份、月份、日期、小时、分钟，EXTRACT(field FROM source)是最清晰的方式：

SELECT 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(MINUTE FROM now()) AS minute, EXTRACT(SECOND FROM now()) AS second, EXTRACT(QUARTER FROM now()) AS quarter, EXTRACT(DOY FROM now()) AS day_of_year, EXTRACT(WEEK FROM now()) AS week_number;

这个函数支持很多字段，我平时最常用的有：

• YEAR、MONTH、DAY：基本日期分量
• HOUR、MINUTE、SECOND：时间分量，SECOND 会带小数
• QUARTER：1~4
• DOY：一年中的第几天，1~366
• DOW：一周中的第几天，周日为 0，周六为 6
• ISODOW：ISO 8601 标准，周一为 1，周日为 7（算工作日排序时更省心）
• WEEK：ISO 周数
• EPOCH：1970-01-01 以来的总秒数，对 timestamp 和 timestamptz 均适用

EXTRACT返回的是numeric类型，这个细节很重要，比如拿来做除法时，要留意会不会产生小数。如果你想拿到整数，建议显式转::int。

date_part(field, source)和EXTRACT功能几乎一致，只是参数顺序反过来：

SELECT date_part('year', now());

它返回double precision类型。两者在实际使用中我一般统一用EXTRACT，因为在 GROUP BY 里写EXTRACT(YEAR FROM created_at)比字符串参数更不容易打错。

3.2 dow 和 isodow：谁才是周一？

这是时间提取里最容易闹乌龙的地方。一个看似毫不起眼的字段DOW，小学的时候还要求“星期日是每周第一天”，但如果统计周报时默认DOW是周一，那整个报表从周日开始就错位了。

PostgreSQL 里：

• DOW：0 表示星期日，1~6 表示星期一至星期六
• ISODOW：1 表示星期一，7 表示星期日，ISO 8601 标准

举一个例子：今天是周六，EXTRACT(DOW FROM now())返回 6，EXTRACT(ISODOW FROM now())返回 6，Weekday 的映射只在周日那天的 0 与 7 上不同。很多统计周报的程序员写“周一到周日”时如果用了DOW，会把周日归到新的一周的第一天，这样很隐蔽，容易漏数。

我的建议是，凡是处理“周”维度的统计，直接使用ISODOW。比如按周分组看活跃：

SELECT EXTRACT(YEAR FROM created_at) AS year, EXTRACT(WEEK FROM created_at) AS week, EXTRACT(ISODOW FROM created_at) AS dow, COUNT(*) FROM user_actions GROUP BY 1, 2, 3 ORDER BY 1, 2, 3;

EXTRACT(WEEK FROM ...)在 PG 中本身就是 ISO 周规则，周一是第一天，年初的周归属也按 ISO 标准判断。所以如果你用WEEK分组，又用DOW判断星期几，会出现两者的“周起始”规则不一致的冲突。这是非常隐蔽的坑，用ISODOW就能避免。

3.3 to_char：想要什么格式都行

如果说EXTRACT是用来做数值提取的，那to_char就是用来做“格式美化”的。它是 PG 的万能格式化函数，能把时间转换成任意你想要的字符串格式：

SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS'), to_char(now(), 'YYYY-MM-DD'), to_char(now(), 'HH24:MI'), to_char(now(), 'Month DD, YYYY'), to_char(now(), 'Dy, HH12:MI:SS AM');

常见的模板模式：

• YYYY：四位年份
• MM：月份，01~12
• DD：日
• HH24：24小时制小时
• HH12：12小时制小时
• MI：分钟
• SS：秒
• MS：毫秒，三位的补零
• US：微秒，六位的补零
• TZ：时区缩写
• Day：星期的英文全称（注意首字母大写，宽度补齐到9个字符）
• Dy：星期的英文缩写

to_char在日志文件名、导出报表、向上展示日期时间的时候是主力函数。它的好处是最终输出已经天然对齐宽度和补零，省去应用层再补 0 的逻辑。另一个非常好用的场景是“转成字符串后作为分组键”，比如按天分组的日报：

SELECT to_char(created_at, 'YYYY-MM-DD') AS day, COUNT(*) FROM orders WHERE created_at >= now() - interval '30 days' GROUP BY 1 ORDER BY 1;

不过要注意，一旦用了to_char(created_at, 'YYYY-MM-DD')作为分组，其实你就是在对列做“表达式分组”，如果表很大，会破坏索引利用效率。后面第 5 章我会讲怎么避免这种问题。

3.4 判断工作日、周末，最直接的表达式

实际业务里经常要判断“这条数据是否产生在周末”。这里分享我直接用EXTRACT(ISODOW ...)的方式来判断：

-- 判定是否工作日 SELECT created_at, EXTRACT(ISODOW FROM created_at) AS dow, (EXTRACT(ISODOW FROM created_at) < 6) AS is_workday FROM orders;

很多人会写EXTRACT(DOW FROM created_at) NOT IN (0,6)来判断工作日，这在 PG 里同样是有效的，但如果你对“周一=1（ISODOW）”更习惯，用ISODOW < 6读起来更直观、不易记混。

同理，判断“是否月初”可以直接：

SELECT EXTRACT(DAY FROM now()) = 1;

判断“是否月末”可以这样：

SELECT (now() + interval '1 day')::date > date_trunc('month', now())::date + interval '1 month';

这个表达式稍微绕一点，思路是“如果明天已经是下个月，那今天就是月末”。这类边界判断常常被写错，建议写成 SQL 后把now()换成几个手工测试日期验证一下。

4. 类型转换与时区：字符串和时间的双向奔赴

4.1 字符串转时间：什么时候用 cast，什么时候用 to_date

从外部系统导数据、从 CSV 文件导入、接收前端参数时，经常要处理“字符串当成时间”的场景。PostgreSQL 的转换非常灵活，也有很多写法：

-- 写法一：直接用 cast SELECT '2025-01-01 10:30:00'::timestamp; -- 写法二：date 类型 SELECT '2025-01-01'::date; -- 写法三：to_date, 适合自定义输入格式 SELECT to_date('2025/01/01', 'YYYY/MM/DD'); -- 写法四：to_timestamp, 适合带自定义格式的字符串 SELECT to_timestamp('2025-01-01 10:30:00', 'YYYY-MM-DD HH24:MI:SS'); -- 写法五：to_timestamp 处理纯数字时间戳 SELECT to_timestamp(1735698600);

从数据库规范的角度，导入数据时我强烈建议使用to_date/to_timestamp显式指定格式，不要依赖隐式转换。因为隐式转换依赖DateStyle会话参数，不同客户端的DateStyle可能不一样，同样的字符串在不同环境可能会解析出不同的日期。之前我就遇到过一个运维脚本，在某台机器上执行正常，换了一台机器就报错，最后排查就是因为DateStyle不一样。

这里有一个容易踩坑的地方：to_timestamp返回的是timestamptz，它会把字符串按当前会话时区解释并转成对应的时间戳；to_date返回date类型，是没有时区概念的。如果你要导入的是一个绝对时刻（比如“北京时间 2025-01-01 10:30:00”），需要使用to_timestamp，并把会话时区设置正确。如果要导入的是一个日历日期（比如“2025-01-01”表示一个日期而非时刻），就用to_date。

4.2 时区处理：AT TIME ZONE 的正确用法

AT TIME ZONE是 PG 里非常有辨识度的一个语法，我用它解决过很多“跨时区统计”的问题。它的核心功能是把一个带时区的时间戳转换到指定时区的“墙上时钟时间”，或者反过来把一个不带时区的本地时间转换成某个时区的绝对时刻。看例子：

-- 将 timestamptz 转为指定时区的本地时间（返回 timestamp） SELECT now() AT TIME ZONE 'Asia/Shanghai'; -- 将 timestamp（不带时区）按指定时区转为 timestamptz SELECT timestamp '2025-01-01 10:00:00' AT TIME ZONE 'Asia/Shanghai';

第一种写法特别适合“统一换算成北京时间出报表”。如果你的业务用户都在东八区，而数据库服务器时区是 UTC，那直接now()出来的字段在 pgAdmin 里可能就是 UTC 时间（如果会话时区是 UTC）。这个时候：

SELECT now() AT TIME ZONE 'Asia/Shanghai' AS beijing_time;

就能让报表侧拿到一个不带时区的北京时间本地字符串。这个返回类型是timestamp（不带时区），它的语义已经包含了转换后的墙上时间，所以不会再被客户端的时区相关设置二次变换，展示上比较省心。

但反过来，如果你要把“北京时间 2025-01-01 10:00:00”存成一个绝对时刻，正确做法是：

SELECT timestamp '2025-01-01 10:00:00' AT TIME ZONE 'Asia/Shanghai';

以上操作坑在哪里？关键就是你得先弄清楚自己是“从绝对时刻转本地时间展示”，还是“从本地时间转绝对时刻存储”，两者互为逆操作。我见过不少同事在这两个方向之间反复折腾，最后时间仍然差了 8 个小时。判断方式很简单：AT TIME ZONE左侧是timestamptz，结果就是timestamp（本地时间）；左侧是timestamp，结果就是timestamptz（转为绝对时刻）。一旦搞混，就先从结果类型去倒推。

4.3 实战：按周、月、季度分组的唯一推荐写法

顺着时区的话题，我直接给出一套生产环境可用的按周期汇总 SQL。比如我要统计过去 12 个月的订单金额，按自然月分组，并以自然月展示“YYYY-MM”：

SELECT to_char(date_trunc('month', created_at AT TIME ZONE 'Asia/Shanghai'), 'YYYY-MM') AS month, SUM(amount) AS total_amount FROM orders WHERE created_at >= date_trunc('month', now() AT TIME ZONE 'Asia/Shanghai') - interval '11 months' AND created_at < date_trunc('month', now() AT TIME ZONE 'Asia/Shanghai') + interval '1 day' GROUP BY 1 ORDER BY 1;

这里我故意用了created_at AT TIME ZONE 'Asia/Shanghai'把时间统一转换到东八区后再date_trunc。原因是：如果直接对created_at做date_trunc('month', ...)，它是按数据库会话时区来截断的，一旦会话时区不是东八区，就会把北京时间月初的那笔订单归到上个月去。

按周统计也类似，但更建议把 ISO 年周作为分组键，避免跨年问题：

SELECT EXTRACT(ISODOW FROM created_at AT TIME ZONE 'Asia/Shanghai') AS weekday, COUNT(*) FROM orders WHERE created_at AT TIME ZONE 'Asia/Shanghai' >= date_trunc('week', now() AT TIME ZONE 'Asia/Shanghai') GROUP BY 1;

这种写法的精髓是“先转时区，再截断，再格式化”，顺序不能乱，乱了结果就会偏。

5. 性能提点：时间列查询为什么越来越慢

5.1 时间范围查询的索引友好写法

PostgreSQL 里对时间列建索引是常规操作，但查询时写不好，索引就白建了。我经常见到的一种低效写法是：

-- 不推荐：对列做表达式，索引失效 SELECT * FROM orders WHERE to_char(created_at, 'YYYY-MM-DD') = '2025-01-01';

这种写法让 PG 无法直接使用created_at上的普通索引，因为索引存的是原始created_at值，不是格式化后的字符串。正确的做法是直接对时间列做范围比较：

SELECT * FROM orders WHERE created_at >= timestamp '2025-01-01 00:00:00' AND created_at < timestamp '2025-01-02 00:00:00';

如果你的created_at是timestamptz类型，注意用timestamptz字面量或让 PG 自动转换。善用BETWEEN ... AND也是可以的，但BETWEEN是包含两端边界的，对时间列做等值查询时一般写成>=和<的组合，逻辑更严谨、不易多出边界数据。

5.2 对时间表达式建索引：真的有必要吗

有些业务确实经常按date_trunc('month', created_at)分组，而且表很大，每次都临时算表达式代价高。这种情况下可以建“表达式索引”：

CREATE INDEX idx_orders_created_month ON orders (date_trunc('month', created_at));

建了之后，如果查询的 WHERE / GROUP BY 里也用相同表达式，PG 就会自动用上这个索引。类似的还有日期字段直接按天分组的表达式索引：

CREATE INDEX idx_orders_created_date ON orders ((created_at::date));

但说实话，对于时间维度，我建议优先用原始列的范围查询，只有当EXPLAIN看到明显的 Seq Scan 且慢查询日志频繁出现时，才去考虑表达式索引。否则表数据量不是特别大，统计型报表直接扫全表往往比走索引更快，因为要回表的行太多时 PG 自己的优化器会偏向全表扫描。

5.3 关于分区表：什么时候该把时间列设为分区键

如果你的订单表、日志表动辄几亿行，而且查询基本都有时间过滤条件，可以考虑用 PG 的原生表分区，以时间列作为分区键。下面是 PG 12+ 常用的一种方式（范围分区）：

CREATE TABLE orders ( id bigint, created_at timestamptz ) PARTITION BY RANGE (created_at); CREATE TABLE orders_2025_01 PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2025-02-01'); CREATE TABLE orders_2025_02 PARTITION OF orders FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

查询时如果 WHERE 里有created_at >= '2025-01-15' AND created_at < '2025-02-01'，PG 的“分区裁剪”可以只扫描对应的分区表，速度会明显改善。分区表的管理也方便，比如按月分区，历史分区可以直接DETACH，归档或删除时不用对主表做大量 DELETE。

不过分区并不是银弹。分区键必须是主键或唯一索引的一部分（POSTGRES 的约束），而且前期分区设计如果粒度不对，后面改起来很折腾。我自己的经验是：先确认业务查询 80% 以上都带时间范围条件，表量超过几千万行，再来考虑分区。否则一个普通索引加合理的范围查询就够用了。

6. 常见问题快查：这些坑我替你踩过了

6.1 时间比较老是差8小时，先查会话时区

这是遇到最多的一个现象：表里明明存的是东八区时间，查出来却少了 8 小时。绝大多数情况是session的TimeZone是 UTC。可以用：

SHOW TIMEZONE;

如果结果是UTC，那now()查出来的timestamptz在展示时就会变成 UTC 的墙上时间。解决方式：

SET TIMEZONE TO 'Asia/Shanghai';

或是在连接串里带上options=-c%20timezone%3DAsia%2FShanghai，让整个连接默认为东八区。生产环境我更推荐在 JDBC / 连接配置层统一设置，而不是在每个会话里手工 SET。

6.2 两个时间戳相减后算小时数，为什么结果是整数不是小数

看这段 SQL：

SELECT EXTRACT(EPOCH FROM (now() - created_at)) / 3600 AS hours FROM orders;

EXTRACT(EPOCH FROM ...)返回numeric，除以 3600 后会有小数，没问题。容易出错的是：

SELECT (EXTRACT(EPOCH FROM (now() - created_at)) / 3600)::int AS hours FROM orders;

这样会把小数截断。如果只想要“已过完整小时数”，这个也合理；但如果想要精确到小时带小数，就别转 int。还有一个容易忽略的点：now() - created_at如果是timestamp相减，结果就是interval；如果created_at是date，now() - date的结果也是interval。要保证两边类型匹配，才不容易出现隐式转换带来的意外。

6.3 字符串日期直接比较导致索引失效

慢查询排查时经常看到：

WHERE date_str_col > '2025-01-01'

如果date_str_col是varchar，你拿'2025-01-01'去比较，走的是字符串排序逻辑，和真实日期顺序并不完全一致。更严谨的做法是建表时就使用date/timestamp类型，或至少把这个列转成日期类型并建索引。

6.4 date 类型和 timestamp 比较，隐式转换的坑

PG 允许date和timestamp直接比较，但比较时会做隐式转换：date会被转成当天的00:00:00的timestamp。这种隐式转换如果出现在 WHERE 里，对date字段和timestamp字段之间进行跨类型比较，可能无法命中索引。

例如：

-- created_at 是 timestamp，传了个 date WHERE created_at >= '2025-01-01'

PG 会把'2025-01-01'当date吗？实际会转成timestamp，比较没问题。但如果你对created_at::date做比较，则无法用到普通索引。我的习惯是：在应用层或 SQL 里显式写清楚类型，比如created_at >= timestamp '2025-01-01 00:00:00'，一眼望去就知道边界在哪，避免依赖隐式转换。

6.5 批量事务里 now() 时间不会变

前面提过now()在事务内固定不变，这个问题在“批量插入一批数据，要求每条数据记录各自当前时间”时非常致命。假设在一个事务里循环插入 10 万条日志，日志时间用now()，那么这 10 万条的时间会完全一样。解决办法：

• 插入时用clock_timestamp()
• 应用层每次循环生成当前时间传入

我之前处理过一个案例，业务方要求“数据生成时间尽量精确到毫秒”，当时就是卡在now()的事务特性上。后来把默认值从now()改成了clock_timestamp()，问题就解决了。但这种方案也会导致默认值不是 stable，对计划优化有一点点影响，所以在日志表这种高频插入、顺序性强的表上更合适，在普通业务表上还是保持now()更稳。

6.6 时区转换后分组统计对不齐

常见的跨时区统计错误是：字段是timestamptz，直接date_trunc('day', created_at)，按“数据库时区”的天去截断。如果会话时区是 UTC，而业务用户在中国，那“北京时间今天”从数据库角度看还没到“UTC 的今天”，很多订单会被分到昨天。解决方法就是第 4.3 节那样，先AT TIME ZONE 'Asia/Shanghai'再截断。

判断一个统计查询要不要“先转时区”，只需要问一个问题：这个分组的“天”是按哪个时区的天？用户看到的是东八区，分组时就必须用东八区的边界。

6.7 排查 SQL 问题的实用步骤

如果你在排查时间相关的 SQL，我建议按这个顺序来：

1. SHOW TIMEZONE;先确认会话时区
2. 用SELECT now(), current_date, current_timestamp;看当前时间结果，判断当前会话时间是否正常
3. 选中一条具体的created_at值，用created_at AT TIME ZONE '你的业务时区'看转换结果是否符合直觉
4. 加EXPLAIN看查询是否命中索引，如果走全表扫描，检查 WHERE 里是否对列做了表达式处理
5. 如果是分组统计，把date_trunc截断后的值先肉眼检查一段，再和业务系统里的日历比对

这套顺序我用了好多年，基本能覆盖 80% 的时间类数据问题。

写到最后的小经验

PostgreSQL 的时间函数真正上手之后，你会发现它其实并不难，难的是把“时间语义”想清楚。比如这个时间是绝对时刻还是本地时间，你统计的天是按哪个时区的天，你写的边界是开区间还是闭区间，这些概念一旦理清，函数本身反而没什么可背的。我在项目里总结了一套自己的约定：业务字段用timestamptz，统一存 UTC，展示时按会话时区转，统计时显式指定AT TIME ZONE；凡是区间查询都用>=和<，不依赖BETWEEN；凡是按周统计就用ISODOW。这个约定帮我减少了很多时间相关的返工。如果你还没有自己的一套规范，可以直接拿去用。