前阵子有个朋友在项目群里问了句”我SQL里用了NOW()为什么每次跑出来的结果都不一样“群里瞬间热闹起来有人说不就是这样吗有人说可以加个固定时间但具体怎么固定又讲不清。这个问题让我意识到很多人对PostgreSQL里时间函数的理解其实停留在”能用就行“的阶段。而时间函数恰恰是那种”看着简单、用起来全是细节“的东西——类型选错、时区没管、边界没处理、索引被函数吃掉任何一个环节出问题线上数据就可能悄悄对不上。这篇不打算给你抄一份官方文档上的函数清单。我按自己在实际项目里摸爬滚打的经验把PostgreSQL里和时间相关的函数按“类型基础、时间获取、时间运算、区间查询、格式化、常量时间、踩坑总结”这条线完整梳理一遍。重点回答几个高频问题now()到底能不能固定不变“最近三天”怎么写才不会被索引坑为什么同一个时间字段在不同环境查出来能差8个小时希望你看完能少踩几个我踩过的坑。1. 先认清PostgreSQL的时间类型后面所有函数才用不偏很多人上来就直接学函数结果发现同样的now()在不同表里表现不一样或者两个时间相减算出来的值看不懂。根本原因往往是没先把类型搞明白。PostgreSQL里和时间相关的类型主要有四种timestamp、timestamptz、date、time另外还有一个专门用于时间运算的interval类型。它们不是同一个东西混用的时候会触发隐式转换转换的时机和规则如果没搞清结果就会很诡异。1.1 timestamp和timestamptz别等数据错乱了再回来补课先说最重要的两个timestamp和timestamptz。这两个名字长得像实际语义天差地别。timestamp全称是timestamp without time zone它存的就是你给它的那个字符串不带任何时区信息。比如你插入“2024-01-01 12:00:00”它存的就是这个值谁查都是这个样子。而timestamptz全称是timestamp with time zone它内部会把输入的时间统一换算成UTC存储查询的时候再根据当前会话的时区设置转换成当地时间显示。我说个实际例子。之前有个项目订单表用了timestamp类型存下单时间前端传的是“2024-06-01 12:00:00”后端直接用String接住存进去了一切正常。后来公司业务扩张服务器部署到海外前端和服务端之间的时区开始不一致传进来的时间字符串本身就带了时区偏移结果落库的值乱成一团。排查到最后问题就出在timestamp类型上没有时区概念你传什么它存什么根本不会帮你做归一化。所以我的建议非常直接凡是记录“某个时刻”的字段一律用timestamptz。尤其是订单时间、操作时间、登录时间这种跨地域、跨环境都要统一解读的数据timestamptz是唯一安全的选择。timestamp只适合那些不带时区含义的场景比如排课表里的“每周一上午10点”、闹钟里的“每天早上7点”这种和地理位置无关的本地时间概念。1.2 date、time、interval的实际分工date就是纯日期精度到天time是纯时间精度可以到微秒interval是时间间隔专门用来做时间加减。这三者本身不难难的是和timestamp混用时的行为。比如date和timestamp做加减结果会向timestamp靠拢time和interval相加会得到新的time。这些隐式转换规则如果你不主动去记很容易在SQL里写出“看起来对、实际偏离预期”的表达式。interval尤其值得单独说一下。它是PostgreSQL时间运算的灵魂写法灵活到让人眼花缭乱interval 1 dayinterval 3 daysinterval 1 hour 30 minutesinterval 1 year 2 months还可以组合单位interval 2 days 03:30:00。在运算的时候PostgreSQL会把interval按日历规则换算。比如“1 month”加到1月31日上结果是2月28日或29日不会报错也不会变成3月3日。这个特性在你处理账单周期、订阅到期日这种场景时特别有用但在做精确到秒的时间差计算时反而可能因为日历换算带来偏差下面会专门讲。2. 获取当前时间的高频函数now()、CURRENT_TIMESTAMP、clock_timestamp()之间的差异获取当前时间大概是使用频率最高的操作了。PostgreSQL里面有好几个“当前时间”函数但它们的性格完全不一样。最常用的是now()还有一个叫clock_timestamp()另外还有CURRENT_TIMESTAMP、CURRENT_DATE、CURRENT_TIME、statement_timestamp()、transaction_timestamp()。这么多函数如果只知道now()遇到“同一个事务里多次调用时间是否一致”这种需求时就会很被动。2.1 同是“当前时间”性格大不相同先给结论now()就是transaction_timestamp()的别名它返回的是当前事务开始的时间也就是说在一个事务内部无论调用多少次返回的值都一样。这个特性非常有用它保证了同一批操作里写入的时间戳是一致的。而clock_timestamp()返回的是调用那一刻的真实时钟时间同一事务里多次调用结果会递增哪怕你只隔了0.1秒。再看statement_timestamp()它返回的是当前SQL语句开始执行的时间和事务开始时间可能不同因为一个事务里可能有多条SQL。CURRENT_TIMESTAMP和now()一样返回事务开始时间CURRENT_DATE返回当前日期CURRENT_TIME返回当前时间带时区。我用一个表格把这些函数的差异整理出来函数名返回内容在同一个事务内多次调用常用场景now() / CURRENT_TIMESTAMP带时区时间保持不变记录操作时间、默认值clock_timestamp()带时区时间每次变化统计SQL执行耗时、调试statement_timestamp()带时区时间按语句变化和具体SQL执行时刻相关CURRENT_DATE日期保持不变按天分区、当日数据查询CURRENT_TIME带时区时间保持不变只关心时刻不关心日期这里有个特别容易忽视的坑在一条SQL语句里使用now()整个语句中也是固定的因为一条语句自己就是一个隐式事务。但在autocommit模式下每条独立的SQL之间的now()值是会变化的因为它们属于不同事务。只有你显式用BEGIN开启事务把多条SQL包在同一个事务里now()的值才完全恒定。2.2 interval的正确使用姿势直接加减与make_interval有了interval时间加减就变得很直观。最常见的写法是SELECT NOW() INTERVAL 1 day; SELECT NOW() - INTERVAL 30 minutes; SELECT CURRENT_DATE INTERVAL 1 year;但我在实际项目里更推荐一种写法就是用make_interval函数尤其是参数来自外部输入的时候SELECT NOW() make_interval(days 3); SELECT NOW() make_interval(hours 12, mins 30);好处很明显参数可以被变量或应用层传入不用拼字符串也避免了interval 1 day这种写法里单引号内的拼接注入和格式错误。还有一个小细节interval可以在数字后面直接加单位NOW() 3 * INTERVAL 1 day等价于加3天。这种写法在计算“近N天”区间时很常用N由动态参数传进来时特别方便。2.3 age()和直接相减时间差要按场景选计算两个时间之间的差值最直接的是直接用减号。timestamptz减timestamptz得到的是interval这个interval会精确到微秒适合用来算耗时。但很多人会遇到一个困惑两个时间相减结果可能是“1 day 02:30:00”也可能是“30 days 05:00:00”这个结果展示方式不太“人类”。这时候就要用到age()函数。age(end, start)返回的是按年月日分隔的interval比如“1 year 2 mons 3 days 04:00:00”。它的特点是按日历方式计算月、年的差值所以在计算人的年龄、工龄、合同剩余月份这类需求时特别合适语义清晰。但它不适合算精确耗时因为它是日历格式月份和年份的天数不固定没法直接换算成总秒数。实际项目里我的经验是算耗时、性能统计直接用时间戳相减然后提取epoch也就是总秒数SELECT EXTRACT(EPOCH FROM (end_time - start_time)) AS duration_seconds;需要展示成“XX年XX月”这种人性化格式时用age()。比如SELECT age(now(), birthday) AS user_age FROM users;两条路径各司其职不要混用。3. “最近三天”这类查询到底怎么写才靠谱“查最近三天的数据”应该是实际开发中最常见的需求了。但就这么简单一句话写法五花八门性能差异也很大。我看过不少人写WHERE create_time BETWEEN (NOW() - INTERVAL 3 days) AND NOW()也见过有人写WHERE create_time::date CURRENT_DATE - 3还有人写WHERE to_char(create_time, YYYY-MM-DD) 2024-06-01。第一种写法基本没问题后两种在不同情况下都可能惹麻烦。3.1 三天内的几种写法与本质区别先说最推荐的一种WHERE create_time NOW() - INTERVAL 3 days这个写法表达的是“从当前时刻往前推72小时”这个时间段。如果现在时间是6月1日下午3点那么它查的就是5月29日下午3点到现在的数据。对绝大多数业务来说这种口径是准确的用户说“近三天”往往就是“从现在往前算三天”。但如果产品经理说的“近三天”其实是“今天、昨天、大前天”这样的自然日那就要用另一个口径。比如说从6月1日零点往前推3个自然日也就是5月29日零点到6月1日23:59:59。写法应该是WHERE create_time DATE_TRUNC(day, NOW()) - INTERVAL 2 days AND create_time DATE_TRUNC(day, NOW()) INTERVAL 1 day注意这里我用的是和组合而不是BETWEEN ... AND ...。为什么要这样因为create_time DATE_TRUNC(day, now()) INTERVAL 1 day能完整覆盖今天一整天而如果用 2024-06-01 23:59:59这种写法会漏掉带毫秒的记录比如2024-06-01 23:59:59.999。这个边界问题后面还会专门讲。3.2 别对时间列做函数处理索引会直接罢工我看到过太多人写这一类SQLWHERE DATE_TRUNC(day, create_time) CURRENT_DATE WHERE to_char(create_time, YYYY-MM-DD) 2024-06-01 WHERE create_time::date CURRENT_DATE这些写法在数据量小的时候跑得飞快但一旦表数据量上到百万千万级性能就会断崖式下跌。原因很简单你对create_time列做了函数处理之后PostgreSQL的B-tree索引就帮不上忙了因为它索引里存的是原始时间戳没法直接对函数结果做范围查找只能全表扫描每一行算出函数值再比较。正确做法是把函数处理放在等号右侧让列本身保持干净WHERE create_time CURRENT_DATE AND create_time CURRENT_DATE INTERVAL 1 day如果确实需要按天查询还有一种办法是建表达式索引比如CREATE INDEX ON orders (DATE_TRUNC(day, create_time))。但这是特殊场景下的优化手段默认不要用因为每次查询都要考虑能不能命中这个表达式索引维护成本高。3.3 月初、月末、周初等周期边界的通用写法除了一天、三天这种相对时间统计报表里还经常要算月初、月末、周初。这里有几个万能模板我直接给你-- 本月第一天 SELECT DATE_TRUNC(month, CURRENT_DATE); -- 本月最后一天 SELECT DATE_TRUNC(month, CURRENT_DATE) INTERVAL 1 month - INTERVAL 1 day; -- 本周一 SELECT DATE_TRUNC(week, CURRENT_DATE); -- 上周一 SELECT DATE_TRUNC(week, CURRENT_DATE) - INTERVAL 7 days; -- 本季度第一天 SELECT DATE_TRUNC(quarter, CURRENT_DATE);这些表达式之所以可靠是因为date_trunc直接按字段截断不需要自己去拼“2024-06-01 00:00:00”这种字符串也自然处理了跨月、跨年、闰年这些边界情况。比如用DATE_TRUNC(week, 2025-01-01)它返回的是2024年12月30日那一周的周一而不是2025年1月1日本身因为PostgreSQL默认一周从周一开始。这个细节在和业务方对报表数据的时候经常需要解释清楚。4. 让now()不再实时更新固定时间戳的几种落地办法回到开头的热搜问题函数公式now怎么让它不更新实时时间。这个问题在不同场景下有完全不同的解法不能一概而论。先说PostgreSQL里now()的真实行为它不是不稳定函数它是稳定函数STABLE也就是说在一次事务里它是固定的不会每一行都重新取当前时间。但它毕竟代表的是“当前事务开始时间”只要新开事务执行SQL值就会变。4.1 先搞清楚now()在事务里和语句里的行为如果你在一个SQL客户端里执行BEGIN; SELECT now(); -- 等待几秒再执行 SELECT now(); COMMIT;你会发现两条SELECT返回的now()一模一样。这就是事务内固定的含义。但如果你分开执行两条独立的SELECT中间隔了几秒两个值就不一样了。如果在一条INSERT语句里对多行插入都用now()作为默认值这些行的值也是完全一致的因为整条SQL处于同一个事务中。这个特性在很多场景下正好是我们要的比如批量生成订单号时间戳。但如果你是每执行一次SQL就向外部应用返回一个时间然后把这个时间展示给用户那每一次都可能不同用户就会困惑“怎么刷新一下就变了”。4.2 单条SQL内部固定利用事务特性在PostgreSQL的存储过程或函数里如果你希望“本次操作”里所有涉及时间的逻辑都使用同一个值最简单的办法是先把now()取出来放到变量里后面只用变量CREATE OR REPLACE FUNCTION insert_order_and_log(...) RETURNS void AS $$ DECLARE v_now timestamptz : now(); BEGIN INSERT INTO orders (create_time, ...) VALUES (v_now, ...); INSERT INTO order_log (log_time, ...) VALUES (v_now, ...); END; $$ LANGUAGE plpgsql;这样就保证了一个函数内的所有时间戳完全一致而且逻辑上非常清晰。后面如果业务需要调整只需要改这一处赋值不需要动每个INSERT。4.3 跨SQL固定应用层传参才是正道如果你的需求是“在程序里多次执行SQL每次拿到的时间都一样”那就不能依赖数据库函数了正确做法是在应用层生成一次时间然后以参数形式传入每条SQL。比如Java里先用LocalDateTime now LocalDateTime.now()拿一次后面所有SQL都用这个变量绑定参数。这样无论你执行多少条SQL时间都是同一个。很多人有个误解以为只要SQL里不写now()时间就不会变。但等你改成应用层获取时间后又会发现应用服务器有多台每台机器时钟可能有轻微偏差生成的时间还是不完全一致。真正严格的做法是通过一台统一的时间服务获取时间或者在应用层获取后再结合时间校正逻辑。这个精度要求要看业务场景一般业务到毫秒级就够了。还有一种特殊场景报表、BI工具里用某个SQL模板里面写了now()每次刷新报表时间都不一样用户希望固定成“报表生成那一刻的时间”。这种情况最好的处理方式是在BI工具或报表系统的参数里设置一个“查询时间”变量把它绑定为固定的系统当前时间SQL模板里引用这个变量而不是直接写now()。如果工具不支持就在SQL外层包一层先查出来当前时间生成一个临时结果再把这个结果关联进去。总之把“时间点”和“查询逻辑”解耦。4.4 数据表里的固定时间DEFAULT与显式赋值如果你是希望插入到数据表里的时间不要自动更新比如一张表里有create_time但每秒余额变动日志也会更新这个字段导致时间一直变。这个问题的根源在表结构设计不在now()本身。PostgreSQL里没有MySQL那种ON UPDATE CURRENT_TIMESTAMP的自动更新机制所以只要你INSERT时给了create_time它就永远不会变如果你没用DEFAULT插入时没给值它会是NULL而不是自动填当前时间。常见设计是CREATE TABLE orders ( id bigserial PRIMARY KEY, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz );这种情况下created_at一旦插入就固定不变了除非应用层手动UPDATE它。如果你发现created_at在变那一定是应用层有UPDATE操作把它覆盖了跟now()一毛钱关系都没有。检查应用层代码不要把“更新时间”和“创建时间”混在一个字段里。5. 格式化、解析与按时间分组的实战细节时间函数里还有一个大类是格式化和解析。to_char把时间转成字符串to_timestamp把字符串解析成时间。这两类函数看起来机械但格式模板的坑特别多我工作中没少在格式串上栽跟头。5.1 to_char格式模版翻车率最高的几个点先看标准例子SELECT to_char(now(), YYYY-MM-DD HH24:MI:SS); -- 输出: 2024-06-01 14:30:45这个模板能正常工作。但如果你写成YYYY-MM-DD HH:MI:SS小时那部分就变成了12小时制下午2点会显示成02而不是14。这是我在交接代码时最常看到的问题之一上一手开发可能从网上拷了个模板没注意HH和HH24的区别结果所有报表里的时间都差12小时。还有一个点是Mon和Month的区别受系统区域设置影响可能输出英文缩写。比如to_char(now(), Mon DD)在英文环境下输出Jun 01在中文环境下可能输出6月。如果你在导出数据时对月份字符串有格式要求最好固定使用数字月份MM或者用FM前缀去掉前导零to_char(now(), FMMonth DD, YYYY)。5.2 字符串转时间的正确姿势与隐式转换风险把字符串转成时间戳最稳的方式是SELECT to_timestamp(2024-06-01 14:30:45, YYYY-MM-DD HH24:MI:SS);注意to_timestamp返回的是timestamptz类型它会把你给定的字符串按当前会话时区解释。如果你的字符串是本地时间而数据库会话时区设置的UTC结果转出来会偏移。这种问题通常在测试环境不明显一上生产就暴露因为生产和测试的连接时区配置不一样。另外还有一个隐式转换的坑如果把date类型和timestamp类型直接做比较PostgreSQL会把date隐式转换成timestamp再比较也就是默认把日期当成“当天零点”。这在大多数情况下符合预期但如果你是拿一个带业务含义的“日期截止”来和timestamp比较很容易出现边界错误。比如用户选择了开始日期2024-06-01和结束日期2024-06-02你直接写create_time BETWEEN 2024-06-01 AND 2024-06-02结果就是6月2日零点以后的数据全被排除了而业务方想要的很可能包括6月2日一整天。5.3 按天/小时分组统计日期截断才是核心统计类报表里最常用的就是date_trunc。它能把时间戳截断到指定精度是实现按天、按小时、按周分组的标准手段SELECT DATE_TRUNC(day, create_time) AS day, COUNT(*), SUM(amount) FROM orders GROUP BY DATE_TRUNC(day, create_time) ORDER BY day;再配合to_char可以把截断后的日期格式化成更易读的字符串比如to_char(DATE_TRUNC(day, create_time), YYYY-MM-DD)。需要提醒的是date_trunc的粒度参数支持microseconds、milliseconds、second、minute、hour、day、week、month、quarter、year但不包括“半年”这种自定义单位遇到半年需求就自己用CASE WHEN或者EXTRACT计算。6. 实战项目里的时间函数踩坑清单最后这部分把我在项目里遇到过的、可以分享给别人的坑统一整理一遍内容比较杂但每一条都是真实发生过的事。6.1 时区偏差8小时的常见来源PostgreSQL的timestamptz类型本身不存时区信息在字段值里它内部以UTC存储只是在查询输出时根据会话时区转换为本地时间。如果你连接PostgreSQL的客户端时区设置是UTC而业务时区是东八区那么所有timestamptz字段显示出来都会比实际时间慢8小时。排查方法很简单SHOW TIME ZONE; SET TIME ZONE Asia/Shanghai;很多连接池和驱动默认不设置时区会沿用服务器的系统时区。如果服务器是UTC时区业务侧就很容易出现“时间晚8小时”的现象。解决方法是在应用层连接串或驱动配置里显式设置时区或者在数据库初始化脚本里执行SET TIME ZONE Asia/Shanghai。我个人更推荐在连接参数里设置因为数据库全局设置可能会影响其他连接的行为。6.2 时间条件查询的边界写法时间查询的边界问题我在前面已经反复提过这里再总结成一条铁律永远用半开区间[start, end)也就是 start AND end不要用BETWEEN。因为BETWEEN是闭区间会包含end时刻当天的所有时间但更麻烦的是它包含end时刻的整数秒而不包含end时刻的毫秒尾巴这种不对称性特别容易让数据对账时差几条。正确写法示范-- 查询2024年6月1日这一天的数据 SELECT * FROM orders WHERE create_time DATE 2024-06-01 AND create_time DATE 2024-06-01 INTERVAL 1 day;这种写法无论底层数据是精确到微秒还是毫秒都能完整覆盖一整天不会漏也不会多。6.3 时间函数组合使用时的几条经验最后说几条我从项目里总结出来的经验算是时间函数组合使用的习惯性建议第一只要字段是timestamptz任何地方都不要再调用to_char(字段)和date_trunc包住查询列先判断索引是否能命中数据量大的表尤其重要。第二计算时间差时优先用EXTRACT(EPOCH FROM ...)得到总秒数再按需格式化成人类可读的格式。直接用interval做展示不同单位换算时很容易错。第三表结构设计时创建时间和更新时间分开存放创建时间用默认值now()更新时间在应用层显式维护。不要只留一个“时间字段”既当创建又当更新否则审计排查时根本分不清。第四如果代码里需要比对两个时间之间的间隔比如“超过30分钟未支付自动取消”不要用应用层时间去和数据库时间做差而应该全部以数据库时间为准。应用层时钟和数据库时钟不一致在分布式部署下几乎是必然的依赖应用层时间做判断极易出错。写到这里我回看一下这些年处理过的时间函数问题其实很多线上事故的根因都不复杂就是类型选错、时区没统一、边界写错、索引被函数吃掉这四类。把这篇里提到的基础概念和坑点过一遍至少能避开九成以上的时间函数相关故障。实际动手时拿一条真实业务SQL逐段分析比记一百条函数语法都管用。
PostgreSQL时间函数避坑指南：类型、时区与索引全解析
前阵子有个朋友在项目群里问了句”我SQL里用了NOW()为什么每次跑出来的结果都不一样“群里瞬间热闹起来有人说不就是这样吗有人说可以加个固定时间但具体怎么固定又讲不清。这个问题让我意识到很多人对PostgreSQL里时间函数的理解其实停留在”能用就行“的阶段。而时间函数恰恰是那种”看着简单、用起来全是细节“的东西——类型选错、时区没管、边界没处理、索引被函数吃掉任何一个环节出问题线上数据就可能悄悄对不上。这篇不打算给你抄一份官方文档上的函数清单。我按自己在实际项目里摸爬滚打的经验把PostgreSQL里和时间相关的函数按“类型基础、时间获取、时间运算、区间查询、格式化、常量时间、踩坑总结”这条线完整梳理一遍。重点回答几个高频问题now()到底能不能固定不变“最近三天”怎么写才不会被索引坑为什么同一个时间字段在不同环境查出来能差8个小时希望你看完能少踩几个我踩过的坑。1. 先认清PostgreSQL的时间类型后面所有函数才用不偏很多人上来就直接学函数结果发现同样的now()在不同表里表现不一样或者两个时间相减算出来的值看不懂。根本原因往往是没先把类型搞明白。PostgreSQL里和时间相关的类型主要有四种timestamp、timestamptz、date、time另外还有一个专门用于时间运算的interval类型。它们不是同一个东西混用的时候会触发隐式转换转换的时机和规则如果没搞清结果就会很诡异。1.1 timestamp和timestamptz别等数据错乱了再回来补课先说最重要的两个timestamp和timestamptz。这两个名字长得像实际语义天差地别。timestamp全称是timestamp without time zone它存的就是你给它的那个字符串不带任何时区信息。比如你插入“2024-01-01 12:00:00”它存的就是这个值谁查都是这个样子。而timestamptz全称是timestamp with time zone它内部会把输入的时间统一换算成UTC存储查询的时候再根据当前会话的时区设置转换成当地时间显示。我说个实际例子。之前有个项目订单表用了timestamp类型存下单时间前端传的是“2024-06-01 12:00:00”后端直接用String接住存进去了一切正常。后来公司业务扩张服务器部署到海外前端和服务端之间的时区开始不一致传进来的时间字符串本身就带了时区偏移结果落库的值乱成一团。排查到最后问题就出在timestamp类型上没有时区概念你传什么它存什么根本不会帮你做归一化。所以我的建议非常直接凡是记录“某个时刻”的字段一律用timestamptz。尤其是订单时间、操作时间、登录时间这种跨地域、跨环境都要统一解读的数据timestamptz是唯一安全的选择。timestamp只适合那些不带时区含义的场景比如排课表里的“每周一上午10点”、闹钟里的“每天早上7点”这种和地理位置无关的本地时间概念。1.2 date、time、interval的实际分工date就是纯日期精度到天time是纯时间精度可以到微秒interval是时间间隔专门用来做时间加减。这三者本身不难难的是和timestamp混用时的行为。比如date和timestamp做加减结果会向timestamp靠拢time和interval相加会得到新的time。这些隐式转换规则如果你不主动去记很容易在SQL里写出“看起来对、实际偏离预期”的表达式。interval尤其值得单独说一下。它是PostgreSQL时间运算的灵魂写法灵活到让人眼花缭乱interval 1 dayinterval 3 daysinterval 1 hour 30 minutesinterval 1 year 2 months还可以组合单位interval 2 days 03:30:00。在运算的时候PostgreSQL会把interval按日历规则换算。比如“1 month”加到1月31日上结果是2月28日或29日不会报错也不会变成3月3日。这个特性在你处理账单周期、订阅到期日这种场景时特别有用但在做精确到秒的时间差计算时反而可能因为日历换算带来偏差下面会专门讲。2. 获取当前时间的高频函数now()、CURRENT_TIMESTAMP、clock_timestamp()之间的差异获取当前时间大概是使用频率最高的操作了。PostgreSQL里面有好几个“当前时间”函数但它们的性格完全不一样。最常用的是now()还有一个叫clock_timestamp()另外还有CURRENT_TIMESTAMP、CURRENT_DATE、CURRENT_TIME、statement_timestamp()、transaction_timestamp()。这么多函数如果只知道now()遇到“同一个事务里多次调用时间是否一致”这种需求时就会很被动。2.1 同是“当前时间”性格大不相同先给结论now()就是transaction_timestamp()的别名它返回的是当前事务开始的时间也就是说在一个事务内部无论调用多少次返回的值都一样。这个特性非常有用它保证了同一批操作里写入的时间戳是一致的。而clock_timestamp()返回的是调用那一刻的真实时钟时间同一事务里多次调用结果会递增哪怕你只隔了0.1秒。再看statement_timestamp()它返回的是当前SQL语句开始执行的时间和事务开始时间可能不同因为一个事务里可能有多条SQL。CURRENT_TIMESTAMP和now()一样返回事务开始时间CURRENT_DATE返回当前日期CURRENT_TIME返回当前时间带时区。我用一个表格把这些函数的差异整理出来函数名返回内容在同一个事务内多次调用常用场景now() / CURRENT_TIMESTAMP带时区时间保持不变记录操作时间、默认值clock_timestamp()带时区时间每次变化统计SQL执行耗时、调试statement_timestamp()带时区时间按语句变化和具体SQL执行时刻相关CURRENT_DATE日期保持不变按天分区、当日数据查询CURRENT_TIME带时区时间保持不变只关心时刻不关心日期这里有个特别容易忽视的坑在一条SQL语句里使用now()整个语句中也是固定的因为一条语句自己就是一个隐式事务。但在autocommit模式下每条独立的SQL之间的now()值是会变化的因为它们属于不同事务。只有你显式用BEGIN开启事务把多条SQL包在同一个事务里now()的值才完全恒定。2.2 interval的正确使用姿势直接加减与make_interval有了interval时间加减就变得很直观。最常见的写法是SELECT NOW() INTERVAL 1 day; SELECT NOW() - INTERVAL 30 minutes; SELECT CURRENT_DATE INTERVAL 1 year;但我在实际项目里更推荐一种写法就是用make_interval函数尤其是参数来自外部输入的时候SELECT NOW() make_interval(days 3); SELECT NOW() make_interval(hours 12, mins 30);好处很明显参数可以被变量或应用层传入不用拼字符串也避免了interval 1 day这种写法里单引号内的拼接注入和格式错误。还有一个小细节interval可以在数字后面直接加单位NOW() 3 * INTERVAL 1 day等价于加3天。这种写法在计算“近N天”区间时很常用N由动态参数传进来时特别方便。2.3 age()和直接相减时间差要按场景选计算两个时间之间的差值最直接的是直接用减号。timestamptz减timestamptz得到的是interval这个interval会精确到微秒适合用来算耗时。但很多人会遇到一个困惑两个时间相减结果可能是“1 day 02:30:00”也可能是“30 days 05:00:00”这个结果展示方式不太“人类”。这时候就要用到age()函数。age(end, start)返回的是按年月日分隔的interval比如“1 year 2 mons 3 days 04:00:00”。它的特点是按日历方式计算月、年的差值所以在计算人的年龄、工龄、合同剩余月份这类需求时特别合适语义清晰。但它不适合算精确耗时因为它是日历格式月份和年份的天数不固定没法直接换算成总秒数。实际项目里我的经验是算耗时、性能统计直接用时间戳相减然后提取epoch也就是总秒数SELECT EXTRACT(EPOCH FROM (end_time - start_time)) AS duration_seconds;需要展示成“XX年XX月”这种人性化格式时用age()。比如SELECT age(now(), birthday) AS user_age FROM users;两条路径各司其职不要混用。3. “最近三天”这类查询到底怎么写才靠谱“查最近三天的数据”应该是实际开发中最常见的需求了。但就这么简单一句话写法五花八门性能差异也很大。我看过不少人写WHERE create_time BETWEEN (NOW() - INTERVAL 3 days) AND NOW()也见过有人写WHERE create_time::date CURRENT_DATE - 3还有人写WHERE to_char(create_time, YYYY-MM-DD) 2024-06-01。第一种写法基本没问题后两种在不同情况下都可能惹麻烦。3.1 三天内的几种写法与本质区别先说最推荐的一种WHERE create_time NOW() - INTERVAL 3 days这个写法表达的是“从当前时刻往前推72小时”这个时间段。如果现在时间是6月1日下午3点那么它查的就是5月29日下午3点到现在的数据。对绝大多数业务来说这种口径是准确的用户说“近三天”往往就是“从现在往前算三天”。但如果产品经理说的“近三天”其实是“今天、昨天、大前天”这样的自然日那就要用另一个口径。比如说从6月1日零点往前推3个自然日也就是5月29日零点到6月1日23:59:59。写法应该是WHERE create_time DATE_TRUNC(day, NOW()) - INTERVAL 2 days AND create_time DATE_TRUNC(day, NOW()) INTERVAL 1 day注意这里我用的是和组合而不是BETWEEN ... AND ...。为什么要这样因为create_time DATE_TRUNC(day, now()) INTERVAL 1 day能完整覆盖今天一整天而如果用 2024-06-01 23:59:59这种写法会漏掉带毫秒的记录比如2024-06-01 23:59:59.999。这个边界问题后面还会专门讲。3.2 别对时间列做函数处理索引会直接罢工我看到过太多人写这一类SQLWHERE DATE_TRUNC(day, create_time) CURRENT_DATE WHERE to_char(create_time, YYYY-MM-DD) 2024-06-01 WHERE create_time::date CURRENT_DATE这些写法在数据量小的时候跑得飞快但一旦表数据量上到百万千万级性能就会断崖式下跌。原因很简单你对create_time列做了函数处理之后PostgreSQL的B-tree索引就帮不上忙了因为它索引里存的是原始时间戳没法直接对函数结果做范围查找只能全表扫描每一行算出函数值再比较。正确做法是把函数处理放在等号右侧让列本身保持干净WHERE create_time CURRENT_DATE AND create_time CURRENT_DATE INTERVAL 1 day如果确实需要按天查询还有一种办法是建表达式索引比如CREATE INDEX ON orders (DATE_TRUNC(day, create_time))。但这是特殊场景下的优化手段默认不要用因为每次查询都要考虑能不能命中这个表达式索引维护成本高。3.3 月初、月末、周初等周期边界的通用写法除了一天、三天这种相对时间统计报表里还经常要算月初、月末、周初。这里有几个万能模板我直接给你-- 本月第一天 SELECT DATE_TRUNC(month, CURRENT_DATE); -- 本月最后一天 SELECT DATE_TRUNC(month, CURRENT_DATE) INTERVAL 1 month - INTERVAL 1 day; -- 本周一 SELECT DATE_TRUNC(week, CURRENT_DATE); -- 上周一 SELECT DATE_TRUNC(week, CURRENT_DATE) - INTERVAL 7 days; -- 本季度第一天 SELECT DATE_TRUNC(quarter, CURRENT_DATE);这些表达式之所以可靠是因为date_trunc直接按字段截断不需要自己去拼“2024-06-01 00:00:00”这种字符串也自然处理了跨月、跨年、闰年这些边界情况。比如用DATE_TRUNC(week, 2025-01-01)它返回的是2024年12月30日那一周的周一而不是2025年1月1日本身因为PostgreSQL默认一周从周一开始。这个细节在和业务方对报表数据的时候经常需要解释清楚。4. 让now()不再实时更新固定时间戳的几种落地办法回到开头的热搜问题函数公式now怎么让它不更新实时时间。这个问题在不同场景下有完全不同的解法不能一概而论。先说PostgreSQL里now()的真实行为它不是不稳定函数它是稳定函数STABLE也就是说在一次事务里它是固定的不会每一行都重新取当前时间。但它毕竟代表的是“当前事务开始时间”只要新开事务执行SQL值就会变。4.1 先搞清楚now()在事务里和语句里的行为如果你在一个SQL客户端里执行BEGIN; SELECT now(); -- 等待几秒再执行 SELECT now(); COMMIT;你会发现两条SELECT返回的now()一模一样。这就是事务内固定的含义。但如果你分开执行两条独立的SELECT中间隔了几秒两个值就不一样了。如果在一条INSERT语句里对多行插入都用now()作为默认值这些行的值也是完全一致的因为整条SQL处于同一个事务中。这个特性在很多场景下正好是我们要的比如批量生成订单号时间戳。但如果你是每执行一次SQL就向外部应用返回一个时间然后把这个时间展示给用户那每一次都可能不同用户就会困惑“怎么刷新一下就变了”。4.2 单条SQL内部固定利用事务特性在PostgreSQL的存储过程或函数里如果你希望“本次操作”里所有涉及时间的逻辑都使用同一个值最简单的办法是先把now()取出来放到变量里后面只用变量CREATE OR REPLACE FUNCTION insert_order_and_log(...) RETURNS void AS $$ DECLARE v_now timestamptz : now(); BEGIN INSERT INTO orders (create_time, ...) VALUES (v_now, ...); INSERT INTO order_log (log_time, ...) VALUES (v_now, ...); END; $$ LANGUAGE plpgsql;这样就保证了一个函数内的所有时间戳完全一致而且逻辑上非常清晰。后面如果业务需要调整只需要改这一处赋值不需要动每个INSERT。4.3 跨SQL固定应用层传参才是正道如果你的需求是“在程序里多次执行SQL每次拿到的时间都一样”那就不能依赖数据库函数了正确做法是在应用层生成一次时间然后以参数形式传入每条SQL。比如Java里先用LocalDateTime now LocalDateTime.now()拿一次后面所有SQL都用这个变量绑定参数。这样无论你执行多少条SQL时间都是同一个。很多人有个误解以为只要SQL里不写now()时间就不会变。但等你改成应用层获取时间后又会发现应用服务器有多台每台机器时钟可能有轻微偏差生成的时间还是不完全一致。真正严格的做法是通过一台统一的时间服务获取时间或者在应用层获取后再结合时间校正逻辑。这个精度要求要看业务场景一般业务到毫秒级就够了。还有一种特殊场景报表、BI工具里用某个SQL模板里面写了now()每次刷新报表时间都不一样用户希望固定成“报表生成那一刻的时间”。这种情况最好的处理方式是在BI工具或报表系统的参数里设置一个“查询时间”变量把它绑定为固定的系统当前时间SQL模板里引用这个变量而不是直接写now()。如果工具不支持就在SQL外层包一层先查出来当前时间生成一个临时结果再把这个结果关联进去。总之把“时间点”和“查询逻辑”解耦。4.4 数据表里的固定时间DEFAULT与显式赋值如果你是希望插入到数据表里的时间不要自动更新比如一张表里有create_time但每秒余额变动日志也会更新这个字段导致时间一直变。这个问题的根源在表结构设计不在now()本身。PostgreSQL里没有MySQL那种ON UPDATE CURRENT_TIMESTAMP的自动更新机制所以只要你INSERT时给了create_time它就永远不会变如果你没用DEFAULT插入时没给值它会是NULL而不是自动填当前时间。常见设计是CREATE TABLE orders ( id bigserial PRIMARY KEY, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz );这种情况下created_at一旦插入就固定不变了除非应用层手动UPDATE它。如果你发现created_at在变那一定是应用层有UPDATE操作把它覆盖了跟now()一毛钱关系都没有。检查应用层代码不要把“更新时间”和“创建时间”混在一个字段里。5. 格式化、解析与按时间分组的实战细节时间函数里还有一个大类是格式化和解析。to_char把时间转成字符串to_timestamp把字符串解析成时间。这两类函数看起来机械但格式模板的坑特别多我工作中没少在格式串上栽跟头。5.1 to_char格式模版翻车率最高的几个点先看标准例子SELECT to_char(now(), YYYY-MM-DD HH24:MI:SS); -- 输出: 2024-06-01 14:30:45这个模板能正常工作。但如果你写成YYYY-MM-DD HH:MI:SS小时那部分就变成了12小时制下午2点会显示成02而不是14。这是我在交接代码时最常看到的问题之一上一手开发可能从网上拷了个模板没注意HH和HH24的区别结果所有报表里的时间都差12小时。还有一个点是Mon和Month的区别受系统区域设置影响可能输出英文缩写。比如to_char(now(), Mon DD)在英文环境下输出Jun 01在中文环境下可能输出6月。如果你在导出数据时对月份字符串有格式要求最好固定使用数字月份MM或者用FM前缀去掉前导零to_char(now(), FMMonth DD, YYYY)。5.2 字符串转时间的正确姿势与隐式转换风险把字符串转成时间戳最稳的方式是SELECT to_timestamp(2024-06-01 14:30:45, YYYY-MM-DD HH24:MI:SS);注意to_timestamp返回的是timestamptz类型它会把你给定的字符串按当前会话时区解释。如果你的字符串是本地时间而数据库会话时区设置的UTC结果转出来会偏移。这种问题通常在测试环境不明显一上生产就暴露因为生产和测试的连接时区配置不一样。另外还有一个隐式转换的坑如果把date类型和timestamp类型直接做比较PostgreSQL会把date隐式转换成timestamp再比较也就是默认把日期当成“当天零点”。这在大多数情况下符合预期但如果你是拿一个带业务含义的“日期截止”来和timestamp比较很容易出现边界错误。比如用户选择了开始日期2024-06-01和结束日期2024-06-02你直接写create_time BETWEEN 2024-06-01 AND 2024-06-02结果就是6月2日零点以后的数据全被排除了而业务方想要的很可能包括6月2日一整天。5.3 按天/小时分组统计日期截断才是核心统计类报表里最常用的就是date_trunc。它能把时间戳截断到指定精度是实现按天、按小时、按周分组的标准手段SELECT DATE_TRUNC(day, create_time) AS day, COUNT(*), SUM(amount) FROM orders GROUP BY DATE_TRUNC(day, create_time) ORDER BY day;再配合to_char可以把截断后的日期格式化成更易读的字符串比如to_char(DATE_TRUNC(day, create_time), YYYY-MM-DD)。需要提醒的是date_trunc的粒度参数支持microseconds、milliseconds、second、minute、hour、day、week、month、quarter、year但不包括“半年”这种自定义单位遇到半年需求就自己用CASE WHEN或者EXTRACT计算。6. 实战项目里的时间函数踩坑清单最后这部分把我在项目里遇到过的、可以分享给别人的坑统一整理一遍内容比较杂但每一条都是真实发生过的事。6.1 时区偏差8小时的常见来源PostgreSQL的timestamptz类型本身不存时区信息在字段值里它内部以UTC存储只是在查询输出时根据会话时区转换为本地时间。如果你连接PostgreSQL的客户端时区设置是UTC而业务时区是东八区那么所有timestamptz字段显示出来都会比实际时间慢8小时。排查方法很简单SHOW TIME ZONE; SET TIME ZONE Asia/Shanghai;很多连接池和驱动默认不设置时区会沿用服务器的系统时区。如果服务器是UTC时区业务侧就很容易出现“时间晚8小时”的现象。解决方法是在应用层连接串或驱动配置里显式设置时区或者在数据库初始化脚本里执行SET TIME ZONE Asia/Shanghai。我个人更推荐在连接参数里设置因为数据库全局设置可能会影响其他连接的行为。6.2 时间条件查询的边界写法时间查询的边界问题我在前面已经反复提过这里再总结成一条铁律永远用半开区间[start, end)也就是 start AND end不要用BETWEEN。因为BETWEEN是闭区间会包含end时刻当天的所有时间但更麻烦的是它包含end时刻的整数秒而不包含end时刻的毫秒尾巴这种不对称性特别容易让数据对账时差几条。正确写法示范-- 查询2024年6月1日这一天的数据 SELECT * FROM orders WHERE create_time DATE 2024-06-01 AND create_time DATE 2024-06-01 INTERVAL 1 day;这种写法无论底层数据是精确到微秒还是毫秒都能完整覆盖一整天不会漏也不会多。6.3 时间函数组合使用时的几条经验最后说几条我从项目里总结出来的经验算是时间函数组合使用的习惯性建议第一只要字段是timestamptz任何地方都不要再调用to_char(字段)和date_trunc包住查询列先判断索引是否能命中数据量大的表尤其重要。第二计算时间差时优先用EXTRACT(EPOCH FROM ...)得到总秒数再按需格式化成人类可读的格式。直接用interval做展示不同单位换算时很容易错。第三表结构设计时创建时间和更新时间分开存放创建时间用默认值now()更新时间在应用层显式维护。不要只留一个“时间字段”既当创建又当更新否则审计排查时根本分不清。第四如果代码里需要比对两个时间之间的间隔比如“超过30分钟未支付自动取消”不要用应用层时间去和数据库时间做差而应该全部以数据库时间为准。应用层时钟和数据库时钟不一致在分布式部署下几乎是必然的依赖应用层时间做判断极易出错。写到这里我回看一下这些年处理过的时间函数问题其实很多线上事故的根因都不复杂就是类型选错、时区没统一、边界写错、索引被函数吃掉这四类。把这篇里提到的基础概念和坑点过一遍至少能避开九成以上的时间函数相关故障。实际动手时拿一条真实业务SQL逐段分析比记一百条函数语法都管用。