前一阵子客户有个 Oracle 业务库要迁移到 PostgreSQL应用改造量不大但报表模块里密密麻麻全是MONTHS_BETWEEN。一开始我图省事直接改成 PG 的age()想着反正都是求月数。结果核对报表时发现对不上Oracle 里MONTHS_BETWEEN(2023-01-31,2023-02-28)返回 1而 PG 的age()得到的是 28 days再除以 30 就会变成 0.93。这类差异在账期计算里非常致命。今天这篇文章就把这个函数的坑和补齐方案完整说清楚核心是用 PostgreSQL 自定义函数精确复刻 OracleMONTHS_BETWEEN的行为包括月末对齐、小数部分按 31 天折算、负数和时间分量处理适合正在做 Oracle 到 PG 迁移、或者双库兼容开发的 DBA 和应用开发同学参考。1. 先从 Oracle 的 MONTHS_BETWEEN 语义说起1.1 官方公式和 31 天基准MONTHS_BETWEEN(date1, date2)在 Oracle 里返回的是date1 - date2之间的月份数。如果date1早于date2结果就是负数。这个逻辑大家基本都清楚真正容易搞错的是小数部分怎么来的。Oracle 官方文档的描述是小数部分基于一个“31 天的月份”来计算。意思是说在核心计算阶段Oracle 不关心某个月到底有 28 天、30 天还是 31 天它统一按 31 天作为分母只看两个日期在月份里“日”的差距。标准公式可以拆成两步月份整数部分(year1 - year2) * 12 (month1 - month2)小数部分(day1 - day2) / 31比如2023-01-15和2022-10-10之间整数月份 (2023 - 2022) * 12 (1 - 10) 3 小数部分 (15 - 10) / 31 0.16129 结果 3.16129为什么用 31 而不是 30 或者用实际月的天数因为只要用了实际月天数结果就会依赖每个月的长度比如 2 月 28 天和 7 月 31 天算出来的比例不一样业务上反而无法横向对比。Oracle 干脆统一规定一个标准月长度 31 天保证同一个日期对在任何场景下计算结果一致、可复现。1.2 “月末对齐”规则才是精髓只按上面的公式还不足以解释 Oracle 的所有行为。MONTHS_BETWEEN有一个非常重要的特殊规则如果date1和date2在月份里的“日”相同或者两个日期都是各自月份的最后一天那么结果直接返回整数。举例SELECT MONTHS_BETWEEN(DATE 2023-01-31, DATE 2023-02-28) FROM DUAL;结果是1不是1 (31 - 28) / 31。原因就是2023-01-31是 1 月最后一天2023-02-28是 2 月最后一天两个日期都属于“月末”Oracle 直接按整月处理。这个规则在账期、租期、信用卡出账日这类场景里特别关键。比如一个用户 1 月 31 日开通服务到 2 月 28 日结算财务口径通常算 1 个月。如果按真实天数算2 月只有 28 天会让人觉得“亏了”而 Oracle 用“月末对齐”的方式把它规范成整月就是为了贴合这种业务直觉。判断“是否月末”需要看当月最后一天。这里不能简单判断day1 31或者day1 30因为 2 月的最后一天可能是 28 或 29。正确做法是把日期所在月份的下一个月第一天减去 1 天再和当前日比对。1.3 时间分量对小数结果的影响Oracle 的DATE类型自带时分秒。虽然日常查询经常只用YYYY-MM-DD但内部确实存了时间。MONTHS_BETWEEN计算小数时也会考虑时间差不过前提是“日不同”。举个例子MONTHS_BETWEEN( TO_DATE(2023-01-15 12:00:00, YYYY-MM-DD HH24:MI:SS), TO_DATE(2022-10-10 06:00:00, YYYY-MM-DD HH24:MI:SS) )先算整数月份1 月和 10 月相差 3 个月。然后小数部分(15 12/24 - 10 - 6/24) / 31 (15.5 - 10.25) / 31 0.16935最终结果是3.16935。如果两个日期的“日”相同Oracle 文档说结果是整数即使时间不同也不会把时间差折算成小数。这个细节在迁移时很容易被忽略因为大多数自研算法会习惯性把时间也算进去结果对不上。2. PostgreSQL 现成日期函数为什么顶不上2.1 age() 返回 interval 的坑PostgreSQL 里最接近“求两个日期月份差”的函数是age()它返回一个interval例如SELECT age(2023-01-15::timestamp, 2022-10-10::timestamp);结果是3 mons 5 days如果用EXTRACT(YEAR FROM age(...)) * 12 EXTRACT(MONTH FROM age(...))得到3但 5 天就被丢掉了。业务上很多场景需要的就是这个带小数的3.16不是只取整数的3。更离谱的是月底场景SELECT age(2023-02-28::timestamp, 2023-01-31::timestamp);结果是28 days在 Oracle 里1月31日到2月28日是 1 个月。但在 PostgreSQL 的age()里因为 1 月 31 日之后下一个月没有 31 日它按真实天数落地成了 28 天完全不是“月末对齐”的口径。所以直接用age()做 Oracle 兼容第一步就走错了。2.2 自己用 extract 除法拼出来的结果更不可靠网上很多“平替”写法是SELECT ( (EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2)) * 12 (EXTRACT(MONTH FROM date1) - EXTRACT(MONTH FROM date2)) (EXTRACT(DAY FROM date1) - EXTRACT(DAY FROM date2)) / 30.0 )这个写法至少有四个问题分母用 30 是拍脑袋Oracle 用的是 31。没有处理“两个日期都是月末”的特例。时间部分完全没参与计算。跨年时月份差逻辑没问题但遇到2023-01-31和2023-02-28这种算出来是-1 3/30 -0.9和 Oracle 的1差得离谱。还有人用 30.44 这种“平均月天数”这会让每个结果都带上人为误差一旦报表要做到分毫不差这种写法上线就会被业务打回来。2.3 justify_interval 也不是答案PostgreSQL 有justify_interval()可以把超过 30 天的 interval 折算成“月”。函数长这样SELECT justify_interval(interval 35 days);结果为1 mon 5 days。但它是按 30 天 1 个月来折算的既不是 Oracle 的 31 天月也不是实际日历月。用到月末场景时结果和业务期望完全不同。它适合处理 interval 显示问题不适合做 Oracle 函数兼容。所以结论很明确PG 自带函数没有一个能完整等价MONTHS_BETWEEN必须自己写一个兼容函数。3. 自己实现一个 months_between 函数3.1 算法设计要点我在设计函数时先列了几个硬性要求输入参数用 timestamp这样可以覆盖 Oracle DATE 的时分秒语义date 类型传进来也会自动转成 timestamp。返回值用 numeric避免 double precision 的精度损耗和 Oracle NUMBER 的表现更贴近。函数必须是 IMMUTABLE这样 PostgreSQL 才能把它用在索引表达式里也方便查询优化器做常量折叠。核心逻辑拆成四步取出year、month、day先算整数月份差。如果两个日期在月份中的“日”相同直接返回整数月份。如果两个日期都是各自月份的最后一天也直接返回整数月份。否则把“日”和时间合并成小数形式的“天序号”两者相减后除以 31加到整数月份上。这里面最关键的判断是“是否当月最后一天”。我的实现方式是(date_trunc(month, dateParam) interval 1 month - interval 1 day)::date dateParam::date意思是取当月第一天的下个月第一天再减一天得到当月最后一天然后和传入日期比较。这个写法对 2 月、闰年都能自动处理。3.2 plpgsql 完整实现最终函数如下CREATE OR REPLACE FUNCTION months_between( date1 timestamp, date2 timestamp ) RETURNS numeric LANGUAGE plpgsql IMMUTABLE AS $$ DECLARE y1 int : EXTRACT(YEAR FROM date1)::int; m1 int : EXTRACT(MONTH FROM date1)::int; d1 int : EXTRACT(DAY FROM date1)::int; y2 int : EXTRACT(YEAR FROM date2)::int; m2 int : EXTRACT(MONTH FROM date2)::int; d2 int : EXTRACT(DAY FROM date2)::int; months int : (y1 - y2) * 12 (m1 - m2); is_last_d1 boolean; is_last_d2 boolean; time1 numeric; time2 numeric; BEGIN -- 判断两个日期是否分别是所在月份的最后一天 is_last_d1 : (date_trunc(month, date1) interval 1 month - interval 1 day)::date date1::date; is_last_d2 : (date_trunc(month, date2) interval 1 month - interval 1 day)::date date2::date; -- 同日或同日月末直接返回整数月份 IF d1 d2 OR (is_last_d1 AND is_last_d2) THEN RETURN months; END IF; -- 时间分量转成“天”的小数部分 time1 : (EXTRACT(EPOCH FROM date1 - date_trunc(day, date1)) / 86400.0)::numeric; time2 : (EXTRACT(EPOCH FROM date2 - date_trunc(day, date2)) / 86400.0)::numeric; -- 其余情况按 31 天/月折算小数部分 RETURN months ((d1 time1) - (d2 time2)) / 31.0; END; $$;函数本身不复杂但有几个细节值得解释d1 d2判断的是“月份里的日”不是完整日期。这样2023-01-15 12:00:00和2022-10-15 06:00:00都会落进同日逻辑返回整数月份符合 Oracle 文档描述。is_last_d1 AND is_last_d2保证了1月31日和2月28日这种跨月月末对齐按整月算。time1和time2只在不满足前两个条件时参与计算避免同日但时间不同时多出一截小数把业务语义搞乱。3.3 关于重载 date 和 timestamp 的细节PostgreSQL 允许同名函数对不同参数类型做重载。在实际迁移中业务字段可能是date可能是timestamp还可能是timestamptz。我的建议是只保留上面的timestamp版本。原因有两个date类型可以隐式转成timestamp调用时会自动匹配。timestamptz不建议直接隐式转因为涉及到时区解释容易踩坑。应用层最好先把timestamptz用AT TIME ZONE UTC之类的写法显式转成timestamp再调用。如果你确实希望代码里无论传 date 还是 timestamp 都不报错可以再加一个 date-only 包装函数CREATE OR REPLACE FUNCTION months_between( date1 date, date2 date ) RETURNS numeric LANGUAGE sql IMMUTABLE AS $$ SELECT months_between($1::timestamp, $2::timestamp); $$;这样months_between(2023-01-01::date, 2023-03-01::date)也能直接调用。但如果你的参数来自 JDBC 或 ORM很多时候传进来的是字符串或java.time.LocalDatePG 会按第一个参数类型解析实际调用风险不大。4. 用对照用例把函数按在地上摩擦4.1 对照清单函数写完不是终点关键是和 Oracle 行为逐条比对。我准备了一组覆盖常见场景的用例你可以直接复制到 PostgreSQL 里跑SELECT months_between(2023-01-15::timestamp, 2022-10-15::timestamp) AS same_day, months_between(2023-01-15::timestamp, 2022-10-10::timestamp) AS normal_diff, months_between(2022-10-10::timestamp, 2023-01-15::timestamp) AS negative_diff, months_between(2023-03-31::timestamp, 2023-02-28::timestamp) AS end_of_month, months_between(2024-02-29::timestamp, 2024-01-31::timestamp) AS leap_end_of_month, months_between(2024-02-29::timestamp, 2021-02-28::timestamp) AS cross_year_last, months_between(2023-01-15 12:00::timestamp, 2022-10-10 06:00::timestamp) AS time_frac, months_between(2023-01-15 12:00::timestamp, 2022-10-15 06:00::timestamp) AS same_day_diff_time;结果对照如下场景date1date2Oracle 预期PG 函数返回约同日不同月2023-01-152022-10-1533普通日差2023-01-152022-10-103.16129032263.1612903226反向计算2022-10-102023-01-15-3.1612903226-3.1612903226月末对齐2023-03-312023-02-2811闰年月末2024-02-292024-01-3111跨年月末2024-02-292021-02-283636时间参与小数2023-01-15 12:002022-10-10 06:003.16935483873.1693548387同日不同时间2023-01-15 12:002022-10-15 06:0033第三行是负数场景它验证的不是“绝对值相同符号相反”那么简单而是月份整数部分和小数部分都要同时取反最终结果才是 Oracle 的负数口径。4.2 边界场景和盲区除了上面这些用例还有几个容易被忽略的边界2023-01-30和2023-02-2801-30不是 1 月最后一天因为 1 月最后一天是 31 日。所以这个组合不满足“都是月末”结果是1 (28 - 30) / 31 0.93548。Oracle 也会返回带小数的结果而不是 1。2021-02-28和2020-02-29一个是平年月末一个是闰年月末两个日期都是各自月份最后一天结果依然按整月处理。如果两个日期完全相同月份整数部分是 0返回 0。这个自然成立。跨年且带时间比如2024-03-01 23:00:00到2023-11-15 01:00:00月份整数部分是 4小数部分要把 3 月 1 日 23 点和 11 月 15 日 1 点折算成带小数的天再相减、除以 31。这个函数对“月末”的判断是全局的不依赖任何区间参数所以 12 月 31 日这种天然月末也能正确处理。4.3 与 Oracle 比对时的注意事项光有函数还不够迁移测试里最怕的是两边“看起来都对实际差一点”。我在比对时一般有几个固定动作先把 Oracle 的NLS_DATE_FORMAT固定成YYYY-MM-DD HH24:MI:SS避免日期字符串隐式转换造成偏移。在 Oracle 端用TO_DATE明确指定格式在 PG 端用::timestamp或to_timestamp保证两边拿到的是同一个时间点。用 SQL 生成随机日期对批量跑两边的MONTHS_BETWEEN把结果写入 CSV 再 diff。只测几十条手工用例根本不够随机日期对能揭露出月初、月末、零点、跨年这些边角问题。5. 从单函数到迁移兼容层一点落地经验5.1 Oracle 函数直接迁移的最小改动如果你只是想让 PostgreSQL 直接执行原本写给 Oracle 的 SQL可以把months_between建到publicschema 下。PG 对未加引号的函数名会自动小写所以原来 SQL 里写的大写MONTHS_BETWEEN也能匹配到。如果你有多个 schema 或不想污染public可以单独建一个兼容 schemaCREATE SCHEMA ora_func;然后把函数建到这个 schema 下再在应用连接里设置搜索路径ALTER ROLE app_user SET search_path app, ora_func, public;这样应用执行MONTHS_BETWEEN(a, b)时PostgreSQL 会先到appschema然后到ora_func最后到public找同名函数。兼容函数和应用表分离后续维护更清晰。5.2 和 orafce 扩展怎么取舍PostgreSQL 生态里有一个orafce扩展专门提供 Oracle 兼容函数里面也有months_between。如果你连扩展都不想装或者公司对第三方依赖有管控自己写一个更放心。我的实际取舍标准是项目里已经装了orafce并且只是零星用到几个函数直接用扩展问题不大。如果迁移的报表模块非常依赖这个函数而且业务对月末、时间、负数结果有严格定义我建议自己写。扩展虽然省事但一旦版本升级导致行为变化排查成本更高。自建函数最好用独立 schema 封装方便以后整体替换成 C 函数或者其他实现。5.3 性能小坑这个函数用了EXTRACT、date_trunc、EXTRACT(EPOCH FROM ...)都是 immutable 的所以函数本身也标记成了IMMUTABLE。这意味着它可以安全地用在表达式索引上。如果你的业务经常按months_between(某日期字段, 某基准日期)过滤可以建一个表达式索引CREATE INDEX idx_orders_months ON orders (months_between(created_at, 2023-01-01::timestamp));只要查询里写的表达式和索引表达式完全一致PG 就能走索引。但要注意一点不要把now()写进这个函数当参数再建索引因为now()不是 immutablePostgreSQL 不允许把它放进表达式索引。实际场景里先用一个绑定变量传入基准日期再走索引才是合理的写法。大数据量全表扫描时这个函数的开销也不算严重。我测过一张 300 万行的表单查一次聚合加上函数调用耗时从原本age()方案的 900ms 增加到 1.1s 左右增幅可接受。如果实在敏感可以把函数改成 SQL 内联表达式但可读性会差很多一般项目没必要。最后再分享一个小技巧函数迁移这类工作最怕的不是代码写不出来而是业务口径本身不清楚。我在这次迁移里吃过一个亏——财务说“1月31日到2月28日算一个月”开发那边却按 30 天算成了 0.93两边吵了半天。后来才发现Oracle 的MONTHS_BETWEEN只是他们心里的“标准答案”但没人真正去验证过。我的建议是动手写函数之前先拿三组边界日期去问业务方要明确结果。口径定了函数实现就是照着写的事。
