企业官网建设流程全解析

前一阵子客户有个 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 才能把它用在索引表达式里，也方便查询优化器做常量折叠。

核心逻辑拆成四步：

1. 取出year、month、day，先算整数月份差。
2. 如果两个日期在月份中的“日”相同，直接返回整数月份。
3. 如果两个日期都是各自月份的最后一天，也直接返回整数月份。
4. 否则，把“日”和时间合并成小数形式的“天序号”，两者相减后除以 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.LocalDate，PG 会按第一个参数类型解析，实际调用风险不大。

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;

结果对照如下：

第三行是负数场景，它验证的不是“绝对值相同符号相反”那么简单，而是月份整数部分和小数部分都要同时取反，最终结果才是 Oracle 的负数口径。

4.2 边界场景和盲区

除了上面这些用例，还有几个容易被忽略的边界：

• 2023-01-30和2023-02-28：01-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，可以单独建一个兼容 schema：

CREATE 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()不是 immutable，PostgreSQL 不允许把它放进表达式索引。实际场景里，先用一个绑定变量传入基准日期，再走索引，才是合理的写法。

大数据量全表扫描时，这个函数的开销也不算严重。我测过一张 300 万行的表，单查一次聚合加上函数调用，耗时从原本age()方案的 900ms 增加到 1.1s 左右，增幅可接受。如果实在敏感，可以把函数改成 SQL 内联表达式，但可读性会差很多，一般项目没必要。

最后再分享一个小技巧：函数迁移这类工作，最怕的不是代码写不出来，而是业务口径本身不清楚。我在这次迁移里吃过一个亏——财务说“1月31日到2月28日算一个月”，开发那边却按 30 天算成了 0.93，两边吵了半天。后来才发现，Oracle 的MONTHS_BETWEEN只是他们心里的“标准答案”，但没人真正去验证过。我的建议是，动手写函数之前，先拿三组边界日期去问业务方要明确结果。口径定了，函数实现就是照着写的事。