我去年接手一个数据迁移项目时被一批“看起来一模一样、跑起来差距巨大”的SQL折腾到半夜。后来排查到根因全是时间函数写法问题——有人用now()有人用current_date还有人把时间戳当字符串拼最绝的是因为时区设置不一致同样的now()在不同连接里返回的时间差了8小时。今天就借这个标题把PostgreSQL里常用时间函数、时间计算和时间提取的用法整理成一篇能直接对着写的参考手册覆盖数据类型、时间获取、提取截断、间隔计算、实际业务案例和常见坑位适合刚入门PostgreSQL的开发者也适合写过一段时间但总在时间处理上栽跟头的朋友。1. 为什么时间函数是写SQL绕不过去的坎——先从数据类型说起很多从MySQL转过来的朋友上手PostgreSQL第一个不适应的点不是语法而是时间类型比MySQL更“分裂”。PostgreSQL里有date、time、timestamp、timestamptz、interval五种基本时间类型听着复杂实际上每条业务数据都能用它们精确建模但也正因为类型多函数的行为差异才大。你没搞清楚底层类型函数用起来就是猜。1.1 五种时间类型先搞清楚彼此的边界我用一张表把这五种类型的存储内容和典型用途列一下类型存储内容典型用途date年、月、日生日、账单日、自然日统计time时、分、秒、毫秒营业时间、时刻表timestamp不带时区年、月、日、时、分、秒本地时间记录不关心时区timestamptz带时区年、月、日、时、分、秒、时区跨区域业务时间、订单时间、审计时间interval时间长度天、小时、分钟等计算间隔、时间偏移容易让人迷惑的是timestamp和timestamptztimestamptz在内部其实是以UTC时区存储的只是显示的时候会根据当前会话的TimeZone设置转换成对应时区的时间。也就是说你在东八区插入2024-06-01 12:00:0008它在磁盘上存的是2024-06-01 04:00:0000你在东八区查询会显示2024-06-01 12:00:0008但如果把会话时区改成UTC再查就变成了2024-06-01 04:00:0000。这个行为是特性不是Bug但真有不少人因为不理解它在“为什么我查出来的时间少了8小时”里面浪费了大量时间。interval也值得单独说。它不只是“一个数字加单位”而是可以组合存储的比如1 day 02:30:00在逻辑上是一个整体。这意味着你可以用interval 1 day interval 2 hours也可以直接写interval 1 day 2 hours。后面讲时间计算时interval是绝对的核心怎么强调都不为过。1.2 时区不是“加八小时”那么简单说到timestamptz就必须要提时区。很多人把“时区”理解成“偏移量”觉得东八区就是在UTC基础上加8小时。其实在PostgreSQL里时区是一整套规则包括夏令时规则和历史变更。像America/New_York这种命名时区不同日期可能偏移量不同跟08这种固定偏移不是一个概念。业务里最容易踩的坑是数据库服务器时区设置了Asia/Shanghai但是应用连接串里没有显式指定时区或者反过来服务器是UTC应用是东八区。两边一凑存进去的时间字面量没问题查出来展示时“自动”变了。提示如果业务涉及跨时区协作建议统一约定——数据库连接串显式设置时区或者所有时间字段都用timestamptz存展示时在应用层转换。不要一会儿用timestamp一会儿用timestamptz否则“三天内订单”这种查询结果很可能差一天。2. 获取当前时间now()、current_timestamp、clock_timestamp()的差别与选择PostgreSQL里获取当前时间的函数/关键字多得有点“滥用”now()、current_timestamp、current_date、current_time、clock_timestamp()、statement_timestamp()、transaction_timestamp()、timeofday()。第一次看到这么多个我直接懵了。其实它们分三类弄清三类之间的差别选型就清楚了。2.1 六个函数一张表看明白函数/关键字返回类型作用时机实际表现transaction_timestamp()timestamptz事务开始时间事务内多次调用值不变now()timestamptz事务开始时间是transaction_timestamp()的别名事务内不变statement_timestamp()timestamptz当前语句开始时间同一事务内不同语句值可能不同clock_timestamp()timestamptz实际调用瞬间每次调用都返回真实当前时间current_timestamptimestamptz事务开始时间SQL标准写法推荐timeofday()text实际调用瞬间返回文本格式适合人看不适合计算核心区别一句话概括now()和current_timestamp表示“这个事务是从什么时候开始的”而clock_timestamp()表示“我喊它的那一瞬间是几点几分”。事务开始之后就算你执行了很长时间now()依然不会变但clock_timestamp()会随着真实时间走。热搜词里有“函数公式now怎么让他不更新实时时间”在Excel里大家想的是冻结时间在PostgreSQL里now()本来就是“冻结”在事务开始时刻的你不需要做什么它就不动。可能有人困惑的其实是另一种场景在同一个事务里执行多条INSERT想记录每条语句的实际执行时间这时候如果用now()所有记录都会是同一个时间戳应该改用clock_timestamp()或者statement_timestamp()。2.2 业务上到底该用哪一个这就得看需求了。记录订单创建时间应该用事务时间还是语句时间我个人推荐用now()或者current_timestamp作为默认。理由很实在同一事务内多条INSERT语句如果业务上属于同一个“操作”那它们的时间戳应该一致这样后续对账、排查才能对得上。如果每条记录都要精确到语句执行时刻反而会让一批数据的时间戳分散开不利于按批追踪。比如插入订单头和订单明细BEGIN; INSERT INTO orders (id, created_at) VALUES (1, now()); INSERT INTO order_items (order_id, created_at) VALUES (1, now()); COMMIT;这里两条记录的created_at完全一样因为它们在同一个事务里。这是好事不是坏事。如果你用clock_timestamp()可能就差了几毫秒看着“更真实”但排查问题时反而麻烦。我实际写生产代码时默认都用now()只有做性能测试、计算某条SQL到底跑了多久时才用clock_timestamp()取差值。2.3 now() vs current_date当天的边界问题按自然日统计时最怕边界条件。current_date返回的是date类型也就是当天零点到次日零点之间的那个“日期值”。很多人写“今天的数据”时这么写SELECT count(*) FROM orders WHERE created_at current_date;这个SQL大概率查不到数据。原因在于orders.created_at如果是timestamp或timestamptz类型它包含时分秒而current_date是纯日期两者比较时current_date会被隐式转换成2024-06-01 00:00:00所以只有恰好零点零分零秒的订单才能被匹配到。正确写法是用范围条件SELECT count(*) FROM orders WHERE created_at current_date AND created_at current_date 1;这个写法的精妙之处在于current_date 1在PostgreSQL里可以直接把日期加一天等价于“明天零点”。这种方式既准确又能让索引在created_at上正常工作后面的章节会再展开讲索引相关的问题。3. 时间提取与截断extract、date_part、date_trunc的使用场景拆解时间函数使用频率最高的场景除了筛选范围就是从时间戳里提取“年、月、日、小时、季度”等维度或者把时间截断到某个精度去做分组统计。这类需求在PostgreSQL里主要靠三个函数extract、date_part、date_trunc。3.1 extract 和 date_part季度、星期几、ISO年份extract(field FROM source)是SQL标准语法date_part(field, source)是PostgreSQL的传统写法两者的功能和返回值基本一样唯一的区别是参数顺序extract把字段放前面date_part把来源放前面。我习惯用extract因为可读性更好FROM读起来像自然语言。实际开发中常用的字段有这些字段含义示例year年份extract(year from now())返回 2024month月份extract(month from now())返回 1 到 12day月份中的第几天extract(day from now())返回 1 到 31dow星期几周日为0extract(dow from now())周日返回0周一返回1isodow星期几周一为1extract(isodow from now())周一返回1周日返回7doy一年中的第几天extract(doy from now())返回 1 到 366weekISO周数extract(week from now())返回 1 到 53quarter季度extract(quarter from now())返回 1 到 4hour小时extract(hour from now())返回 0 到 23minute分钟extract(minute from now())返回 0 到 59second秒含小数extract(second from now())返回 0 到 59.999...epoch从1970-01-01 00:00:00 UTC开始的秒数extract(epoch from now())返回一个很大的浮点数两个容易混淆的地方我必须单独拎出来说。第一dow和isodow都表示星期几但起始日不同。dow遵循西方习惯周日是0isodow遵循ISO标准周一是1。如果你按“周一作为一周开始”做周报统计用dow就会把周一当成1、周日当成7算出来的一周周期是从周日开始的数据分组就差了一天。我建议统一用isodow国内业务自然周基本都是从周一开始算的。第二extract(day from interval)里的day不是“这个interval跨了多少天然后取模”而是直接返回这个interval里的“天”部分。比如extract(day from interval 45 days)返回45而不是15。如果你想知道两个时间之间总共差了多少天应该用extract(epoch from ...)再除以86400或者直接用下面会讲的age()和相减运算。3.2 date_trunc报表分组统计的大杀器date_trunc(field, source)的作用是把一个时间戳截断到指定精度的起点返回结果仍然是时间戳类型。比如date_trunc(day, 2024-06-01 14:23:45::timestamp) -- 返回 2024-06-01 00:00:00 date_trunc(month, 2024-06-15 09:30:00::timestamp) -- 返回 2024-06-01 00:00:00 date_trunc(hour, 2024-06-01 14:23:45::timestamp) -- 返回 2024-06-01 14:00:00这个函数对报表统计特别有用因为按天、周、月、季度分组时你不需要先extract出年月日再拼接回去直接date_trunc就可以了。举个例子按天统计每天的订单量SELECT date_trunc(day, created_at) AS day, count(*) AS order_cnt FROM orders WHERE created_at current_date - interval 30 days GROUP BY date_trunc(day, created_at) ORDER BY day;注意这里有个小细节date_trunc(day, created_at)返回的是timestamptz类型展示出来可能会带时区偏移看着像2024-06-01 00:00:0008。如果只需要日期可以再包一层::date转换。不过分组统计其实不需要转因为2024-06-01 00:00:0008和2024-06-01 00:00:0000在分组时会按实际UTC瞬间比较同一个自然日内不会分叉只要你的时区设置是稳定的。3.3 提取之后直接参与运算的例子提取字段除了用于展示、分组还能参与运算。比如统计每个小时段的平均订单金额可以提取小时SELECT extract(hour from created_at) AS hour_of_day, avg(amount) AS avg_amount FROM orders GROUP BY extract(hour from created_at) ORDER BY hour_of_day;再比如判断某条记录是否在“当前季度的最后一天”可以结合date_trunc和日期加减SELECT * FROM projects WHERE deadline::date (date_trunc(quarter, now()) interval 3 months - interval 1 day)::date;这个表达式的意思是当前季度的第一天加上三个月再减一天得到当前季度的最后一天。用date_trunc(quarter, now())先拿到季度起点再做interval偏移逻辑就很清晰了。4. 时间计算与偏移interval、age() 与日期间隔的高效写法时间计算是日常报表里最常摩擦的地方业务方甩过来一句话“给我看最近三个月的月活”你就得跟interval过日子。这一节我把interval的写法、时间相减的返回值、age()函数的坑一次性讲透。4.1 interval 的正确打开方式interval的常见写法有两种一种是用字符串字面量一种是用间隔表达式。字符串字面量最直观但要注意单位单词尽量写全称避免和未来的PostgreSQL版本关键字冲突。我一般这样用-- 三天前 now() - interval 3 days -- 两小时三十分钟后 now() interval 2 hours 30 minutes -- 一个月前的今天 now() - interval 1 month -- 一个季度前 now() - interval 3 months -- 复合写法 interval 1 day 2 hours 3 minutes除了加减interval还支持乘除。比如把1天平均分成6份每份4小时SELECT interval 1 day / 6; -- 返回 04:00:00这个特性在做排班、课时分段这类需求时很有用。4.2 age() 输出的精确年龄直接相减会得到精确到秒的interval但有时候你想要的是“这个人今年多少岁”这种人类友好型的答案。age()就是干这个的。SELECT age(2024-06-01::date, 1995-08-15::date); -- 返回 28 years 9 mons 17 days注意age()参数顺序是被减数在前、减数在后也就是“后面的时间到前面的时间经历了多久”。如果只传一个参数它默认拿当前日期当被减数SELECT age(1995-08-15::date); -- 返回类似 28 years 9 mons 17 days取决于当前日期用age()计算年龄时有个细节要注意age()输出的结果年、月、日是有“进位”关系的比如28年9个月17天。你不能单独拿extract(year from age(...))就当作完整年龄因为如果age()返回28年9个月说明这个人已经过了28岁生日、正在迈向29岁直接从extract(year ...)里取28是对的但如果age()返回27年11个月extract(year ...)返回27而这个人实际上已经过了27岁生日这也是对的因为没过28岁生日。所以取年龄可以直接用extract(year from age(birthday))它是正确的。我不推荐用“当前年份减出生年份”的写法因为生日还没到的时候它会多算一岁。age()已经处理了这种边界直接用就行。4.3 时间戳相减到底返回什么这是新手最容易困惑的地方。timestamp - timestamp返回的是intervaldate - date返回的是整数天数timestamp - date返回的是interval。看几个例子SELECT 2024-06-10 12:00:00::timestamp - 2024-06-01 00:00:00::timestamp; -- 返回 9 days 12:00:00这是 interval SELECT 2024-06-10::date - 2024-06-01::date; -- 返回 9这是整数天数 SELECT 2024-06-10 12:00:00::timestamp - 2024-06-01::date; -- 返回 9 days 12:00:00这是 interval如果你想算“两个时间戳之间差了多少秒”最稳的写法是用epoch提取SELECT extract(epoch from (2024-06-10 12:00:00::timestamp - 2024-06-01 00:00:00::timestamp)); -- 返回 820800 秒extract(epoch from interval)返回的是这个interval的总秒数不受年月日进位影响。这个写法在计算服务响应时间、任务耗时、合约剩余秒数时极其常用。提示不要直接用extract(day from interval)去算两个时间戳之间的天数它会忽略时分秒部分只取“天”这个整数导致结果少算半天甚至一整天。5. 实战案例集合按天/周/月统计、环比与三个月内的数据筛选这一节直接从业务需求出发给出一批可以直接改改就能用的SQL模板。我尽量把条件写法和索引友好的写法都放进来你可以根据自己的业务结构调整字段名。5.1 “三天内/本周/本月”的条件怎么扫“三天内数据”是热搜里的一个典型需求但“三天内”这个说法其实有歧义。是指“最近72小时”还是“今天、昨天、前天这三个自然日”两者在业务含义上很不一样。最近72小时精确到当前时刻往前推72小时SELECT * FROM orders WHERE created_at now() - interval 72 hours;最近三个自然日从今天零点往前推3天SELECT * FROM orders WHERE created_at current_date - interval 3 days;这两种写法都能用上created_at上的普通B-tree索引。但下面这种写法就不行因为它在列上包了函数-- 不推荐索引失效 SELECT * FROM orders WHERE date_trunc(day, created_at) current_date - interval 3 days;本周数据用date_trunc(week, now())SELECT * FROM orders WHERE created_at date_trunc(week, now());注意date_trunc(week, now())返回的是本周周一零点如果你的业务周从周日开始就得用date_trunc(week, now()) - interval 1 day。本月数据用date_trunc(month, now())SELECT * FROM orders WHERE created_at date_trunc(month, now());上月数据SELECT * FROM orders WHERE created_at date_trunc(month, now()) - interval 1 month AND created_at date_trunc(month, now());5.2 分组统计的完整模板按天、周、月分组是统计报表的标配。核心思路就是先date_trunc对齐时间点再聚合。我贴一个按周统计的例子SELECT date_trunc(week, created_at) AS week_start, count(*) AS order_cnt, sum(amount) AS total_amount FROM orders WHERE created_at date_trunc(week, now()) - interval 4 weeks GROUP BY date_trunc(week, created_at) ORDER BY week_start DESC;如果统计结果需要补零比如某天没有订单也要显示0就需要配合generate_series生成一个完整的时间序列再左连接业务表SELECT days.day::date, count(o.id) AS order_cnt FROM generate_series( current_date - interval 6 days, current_date, interval 1 day ) AS days(day) LEFT JOIN orders o ON o.created_at days.day AND o.created_at days.day interval 1 day GROUP BY days.day::date ORDER BY days.day::date;generate_series是PostgreSQL里填补缺失时间桶的利器比在应用层拼日期列表省事得多。环比“本月 vs 上月”SELECT sum(amount) FILTER (WHERE created_at date_trunc(month, now()) AND created_at date_trunc(month, now()) interval 1 month) AS month_amount, sum(amount) FILTER (WHERE created_at date_trunc(month, now()) - interval 1 month AND created_at date_trunc(month, now())) AS last_month_amount FROM orders WHERE created_at date_trunc(month, now()) - interval 1 month;这里用了FILTER子句做条件聚合比CASE WHEN写在sum里更清爽也是PostgreSQL相比MySQL一个很实用的语法特性。5.3 时间转换格式化的常用配方业务方经常让把时间显示成“2024年06月01日”这种中文格式或者按“YYYY-MM-DD HH24:MI:SS”导出Excel。to_char()就是干这个的SELECT to_char(now(), YYYY-MM-DD HH24:MI:SS); -- 返回 2024-06-01 14:23:45 SELECT to_char(now(), YYYY年MM月DD日); -- 返回 2024年06月01日 SELECT to_char(now(), IW) || 周; -- 返回 ISO 周数格式化模板里YYYY是四位年份MM是两位月份DD是两位日期HH24是24小时制小时MI是分钟SS是秒。注意MI是“分钟”不是“月份”很多人写成MM当分钟用结果输出的分钟变成月份直接乱了。格式反转也常用把外部传入的字符串解析成时间SELECT 2024-06-01 14:23:45::timestamp; SELECT to_timestamp(2024-06-01 14:23:45, YYYY-MM-DD HH24:MI:SS);to_timestamp比直接::timestamp转换更灵活可以处理一些非标准格式。但能直接cast就尽量直接cast性能更好。6. 六个最容易踩的时间函数坑时区、索引与边界条件这一节是实战经验的沉淀。下面这些坑我在生产环境和社区答疑里见过太多次每个都值得你收藏起来对照检查。6.1 坑一函数包裹索引列查询直接变全表扫描这是性能问题重灾区。需求是“查今天创建的订单”新手最爱写成SELECT * FROM orders WHERE date_trunc(day, created_at) current_date;结果是就算你在created_at上建了索引PostgreSQL也没法用因为索引里存的是原始时间戳不是date_trunc之后的值。数据库得先把每一行的created_at都算一遍date_trunc再和current_date比较等于把全表扫一遍。正确写法是范围扫描SELECT * FROM orders WHERE created_at current_date AND created_at current_date 1;同样的道理也适用于extract、to_char。原则就一条不要对列本身做函数运算把条件改造成对列的原始值做范围比较。如果有些场景实在需要在函数结果上过滤也不是完全没有优化空间可以建表达式索引CREATE INDEX idx_orders_created_day ON orders (date_trunc(day, created_at));但能避免就避免因为表达式索引对写入性能和查询规划器的友好程度都不如普通索引。6.2 坑二时区不一致导致报表少一天数据我处理过最典型的案例是这样的应用服务器时区是东八区数据库连接串里没指定时区PostgreSQL实例的默认时区是UTC。业务代码插入created_at时用的是now()这个值在数据库里存成了UTC时间查询时用current_date判断“今天”而current_date是由数据库会话时区决定的如果会话是UTC那“今天”就变成UTC的今天和业务方说的“今天”差了8小时。现象就是国内早上8点前的订单在报表里算成了“昨天”。解决方案有两种我推荐双管齐下统一数据库会话时区。在连接串里显式加options-c%20timezone%3DAsia%2FShanghai或者在postgresql.conf里设置timezone Asia/Shanghai。所有业务时间字段统一用timestamptz类型避免在应用层先把时间转成字符串再插入。字符串插入会让数据库丢失“这是哪个时区的时间”的信息。做数据仓库同步时源库和目标库的时区不一致也会导致“明明数据量一样、日期分布却对不上”的诡异现象。要么统一用UTC做ETL中间层要么在抽取SQL里直接转换SELECT created_at AT TIME ZONE Asia/Shanghai AS created_at_sh FROM orders;6.3 坑三now 字符串的隐式转换陷阱有些老代码里能看到这么写INSERT INTO orders (created_at) VALUES (now);这居然能执行成功因为PostgreSQL把字符串now隐式转换成了timestamp类型。但问题在于如果列的声明类型是timestamptz这个字符串会先被转成timestamp不带时区再转成timestamptz整个过程中时区信息可能丢失最终存进去的时间可能和你期待的不一致。正确写法永远是用函数INSERT INTO orders (created_at) VALUES (now()); -- 或 INSERT INTO orders (created_at) VALUES (current_timestamp);搜索引擎里“postgresql安装”“postgresql使用教程”这类热搜词说明很多人刚接触PostgreSQL在这里我先给新手一个忠告不要觉得now能跑就说明它是对的写SQL要养成明确类型、明确函数的习惯。6.4 坑四dow/isodow混用导致周统计错位前面提过dow周日是0isodow周一是1。如果团队里有人用dow判断“是否周一”他可能写的是extract(dow from created_at) 1这在dow语义下返回的是“周二”而不是“周一”。轻则统计错一天重则整个周报的维度全偏。我的建议是代码里统一用isodow并且在SQL注释里标明-- 周一1, 周日7。规范要写进团队约定不然下个接手的人很容易踩。6.5 坑五date_trunc(week) 的起始日与业务周不一致PostgreSQL的date_trunc(week, ...)默认按ISO标准一周从周一开始。但国内有些业务尤其是一些零售、外卖场景习惯把周日作为一周开始。直接用date_trunc(week, now())当“本周起点”就会错位。如果业务周从周日开始需要手动偏移-- 本周起点周日为每周第一天 date_trunc(week, now()) - interval 1 day这个偏移看着不起眼但一旦你在报表里用了它所有按周聚合的指标都得跟着调整。我建议在周报类SQL顶部写清楚业务周的起止规则省得后人看得一头雾水。6.6 坑六日期间隔计算里的“月份”不是你想的那样interval 1 month在PostgreSQL里表示“逻辑月”比如2024-01-31 interval 1 month的结果不是2024-02-31这个日期不存在而是2024-02-29闰年或2024-02-28。这个行为是符合预期的但如果你做的是“固定30天”的订阅周期就千万别用interval 1 month应该用interval 30 days。反过来2024-03-31 - interval 1 month的结果是2024-02-29还是2024-02-28在不同年份、不同边界下可能会有让人惊讶的表现。PostgreSQL会尽量返回一个合法日期但“尽量”不代表“符合你的业务直觉”。涉及账单日、扣款日这种对日期精度很敏感的场景我建议先写个小查询验证一下边界再上线。几个实际操作中的体会最后说点个人体会。第一个是写时间条件 SQL 时脑子里要时刻想着“这条语句能不能命中索引”。created_at ... AND created_at ...这种范围写法不仅是正确性的要求也天然适合B-tree索引等于把正确性和性能一起拿下了。第二个是遇到时间显示不对、差8小时、分组差一天这类问题先别急着改SQL先查SHOW timezone;再查字段类型是timestamp还是timestamptz八成问题就出在这两个地方。第三个是给团队定一个时间处理规范统一类型、统一函数、统一时区这比任何技术方案都省心。时间函数本身不难难的是大家各自用各自的写法最后汇到一张报表里谁也说不清数据是怎么算出来的。