企业官网建设流程全解析

接手过Oracle向PostgreSQL迁移项目的朋友，八成都会在日期函数上栽一回。别的都好说，MONTHS_BETWEEN这个函数，Oracle里用得顺手，换到PostgreSQL里翻遍官方文档也找不到同名函数。业务那边拿着旧SQL来问，报表里“账龄月份数”“工龄月份数”“分期期数”全是靠它算的，你总不能告诉业务说“这个函数没有，你们自己改”。

这篇文章我就把完整的替换方案拆开讲清楚，包括Oracle原函数的行为细节、PostgreSQL里的三种实现思路、可直接复用的完整函数代码，以及我在实际项目中踩过的边界坑。无论你是正在做数据库迁移，还是只想在PostgreSQL里实现一个等价功能，按这篇文章走都能直接落地。

1. 先搞懂Oracle的MONTHS_BETWEEN到底怎么算

1.1 基本规则：整数月份、小数部分和正负号

MONTHS_BETWEEN(date1, date2)返回的是date1和date2之间的月份数，规则是：如果date1晚于date2，结果为正；反之结果为负。两日期如果落在同一个月内的同一天，或者都是各自所在月份的最后一天，结果就是整数；否则会有小数部分。

举几个Oracle实测例子：

• MONTHS_BETWEEN('2023-03-15', '2023-01-15')返回 2，因为正好隔两个月。
• MONTHS_BETWEEN('2023-03-20', '2023-01-15')返回约 2.16129032，多出来的 5 天按 31 天/月折算，即 5/31。
• MONTHS_BETWEEN('2023-01-15', '2023-03-15')返回 -2，顺序反了符号就反了。

关键在于小数部分的计算口径：Oracle按“一个月固定为31天”来折算剩余天数，而不是按实际月份的天数来算。这一点和很多人的直觉不一样，却是整个复刻工作的核心依据。

1.2 月末日期的特殊处理逻辑

这是最容易忽略、也最容易算错的地方。Oracle对“月末”有一套特殊规则：如果两个日期都是各自所在月份的最后一天，Oracle会认为它们之间相隔的是整数个月，忽略实际天数的差异。

看个经典案例：

SELECT MONTHS_BETWEEN(DATE '2023-02-28', DATE '2023-01-31') FROM dual;

这个查询在Oracle中返回 1，而不是 0.9354。为什么？因为2023年1月31日是1月的最后一天，2023年2月28日是2月的最后一天，都是月末，所以Oracle直接判定为整数月。

同理：

SELECT MONTHS_BETWEEN(DATE '2024-02-29', DATE '2023-02-28') FROM dual;

2023年2月28日是2月最后一天，2024年2月29日也是2月最后一天，Oracle返回 12，而不是 12.0322 之类的数字。

这条隐含规则不写进文档里的话，自己闭门造车实现一个函数，大概率会在月末场景上和Oracle对不上账。

1.3 业务场景：为什么老系统到处都在用

这种“月份差”计算在业务系统里太常见了。算工龄要精确到几个月，算贷款利息要把天数转成月数，算账龄得分要看逾期几个月，算会员有效期到没到月份节点，都要靠它。Oracle DBA和开发早就习惯了直接调MONTHS_BETWEEN，很多存量SQL里甚至嵌套了好几层。

所以迁移PostgreSQL时不光要找个替代品，还必须保证“算出来的结果和原来一模一样”，否则报表对不上、利息差几分钱、账龄分错档，业务部门立刻就会找上门。

2. PostgreSQL复刻方案的三种思路

2.1 思路一：年份月份直接相减，天数差除以31

最简单的办法，把年份差乘12加上月份差，再把天数的差值除以31作为小数部分：

SELECT (EXTRACT(YEAR FROM d1) - EXTRACT(YEAR FROM d2)) * 12 + (EXTRACT(MONTH FROM d1) - EXTRACT(MONTH FROM d2)) + (EXTRACT(DAY FROM d1) - EXTRACT(DAY FROM d2)) / 31.0 FROM (SELECT DATE '2023-03-20' AS d1, DATE '2023-01-15' AS d2) t;

这个写法能覆盖大部分“普通日期”场景，代码也短，适合临时核对数据时用。但有两个问题：一是没有处理月末的特殊规则，1月31日到2月28日这种场景会算错；二是公式里EXTRACT要写好几遍，SQL一长可读性很差，还不方便封装成公共逻辑。

2.2 思路二：借助PostgreSQL的AGE函数做基础拆分

PostgreSQL有AGE(date1, date2)函数，直接返回x years y mons z days这样的interval类型。很多人第一反应是“那直接用AGE不就行了”：

SELECT AGE(DATE '2023-03-20', DATE '2023-01-15'); -- 结果: 2 mons 5 days

确实能拆出年月日，但AGE返回的是“整年整月加上剩余天数”，要拼回Oracle那种带小数的月份数，还得自己把剩余天数除以31加上去，而且AGE同样不处理月末对齐问题。AGE更适合展示人类可读的年龄，不适合复刻Oracle的数学计算语义。

2.3 思路三：自定义PL/pgSQL函数，一次封装长期复用

我最终选择的是在PostgreSQL里创建一个自定义函数，命名也叫months_between，参数类型对齐Oracle常用的date和timestamp。这样做的好处很明显：存量SQL只需要把SELECT MONTHS_BETWEEN(a, b)从Oracle原样搬到PostgreSQL，顶多在必要时加个schema前缀，不用改业务逻辑。函数内部集中处理月份差、小数折算、月末判断这些规则，外界不用关心细节。

2.4 三种方案对比与选型建议

如果只是临时对个数，方案一够用。但只要是生产系统迁移，我强烈建议直接上方案三，把函数建好、测试用例跑通，后面所有查询都能复用，一劳永逸。

3. 核心实现：完整函数代码与逐段解析

3.1 基础版函数：先实现主体逻辑

先把一个不算月末特例的基础版本写出来，方便理解整体结构：

CREATE OR REPLACE FUNCTION months_between(date1 date, date2 date) RETURNS numeric LANGUAGE plpgsql IMMUTABLE AS $$ DECLARE year_diff integer; month_diff integer; day_diff numeric; BEGIN year_diff := EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2); month_diff := EXTRACT(MONTH FROM date1) - EXTRACT(MONTH FROM date2); day_diff := EXTRACT(DAY FROM date1) - EXTRACT(DAY FROM date2); RETURN year_diff * 12 + month_diff + day_diff / 31.0; END; $$;

注意返回类型用了numeric而不是double precision，后面计算比值时能拿到更精确的十进制小数，也避免浮点误差给财务、账龄这类场景带来问题。IMMUTABLE标记在第三节单独讲，这里先记住必须加。

3.2 增强版：把月末规则补进去

基础版跑通后，把月末特殊判断加上：

CREATE OR REPLACE FUNCTION months_between(date1 date, date2 date) RETURNS numeric LANGUAGE plpgsql IMMUTABLE AS $$ DECLARE year_diff integer; month_diff integer; day_diff numeric; last_day1 date; last_day2 date; BEGIN year_diff := EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2); month_diff := EXTRACT(MONTH FROM date1) - EXTRACT(MONTH FROM date2); -- 判断date1、date2是否各自所在月份的最后一天 last_day1 := (date_trunc('MONTH', date1) + INTERVAL '1 month - 1 day')::date; last_day2 := (date_trunc('MONTH', date2) + INTERVAL '1 month - 1 day')::date; IF date1 = last_day1 AND date2 = last_day2 THEN -- 两个日期都是月末，Oracle返回整数月 RETURN year_diff * 12 + month_diff; END IF; -- 普通场景：天数差折算成月份 day_diff := EXTRACT(DAY FROM date1) - EXTRACT(DAY FROM date2); RETURN year_diff * 12 + month_diff + day_diff / 31.0; END; $$;

代码逻辑不复杂，核心变化在last_day1和last_day2。date_trunc('MONTH', date)会得到当月1号，再加'1 month - 1 day'这个interval，就精确跳到当月最后一天。这个写法比EXTRACT(DAY FROM (date + INTERVAL '1 month - 1 day'))之类的替代方案要直观得多，也是PostgreSQL里判断月末最稳妥的惯用写法。

3.3 完整版：支持timestamp与默认值

Oracle的MONTHS_BETWEEN既支持date类型，也支持带时分秒的日期时间。为了最大程度对齐，我在参数类型上再适配一下，同时把date类型和timestamp类型都覆盖到。最简单的方法是利用PostgreSQL的函数重载，写两个同名函数：

CREATE OR REPLACE FUNCTION months_between(date1 timestamp, date2 timestamp) RETURNS numeric LANGUAGE plpgsql IMMUTABLE AS $$ DECLARE year_diff integer; month_diff integer; day_frac numeric; last_day1 date; last_day2 date; BEGIN year_diff := EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2); month_diff := EXTRACT(MONTH FROM date1) - EXTRACT(MONTH FROM date2); last_day1 := (date_trunc('MONTH', date1::date) + INTERVAL '1 month - 1 day')::date; last_day2 := (date_trunc('MONTH', date2::date) + INTERVAL '1 month - 1 day')::date; IF date1::date = last_day1 AND date2::date = last_day2 THEN RETURN year_diff * 12 + month_diff; END IF; -- 同时把时分秒带来的天数差算进去 day_frac := (EXTRACT(EPOCH FROM date1) - EXTRACT(EPOCH FROM date2)) / 86400.0; RETURN year_diff * 12 + month_diff + day_frac / 31.0; END; $$; CREATE OR REPLACE FUNCTION months_between(date1 date, date2 date) RETURNS numeric LANGUAGE sql IMMUTABLE AS $$ SELECT months_between(date1::timestamp, date2::timestamp); $$;

第二个函数是个轻量包装，转成timestamp后调用第一个函数，避免两套逻辑各写一遍。EXTRACT(EPOCH FROM ...)把timestamp转成Unix纪元秒数，相减得到秒差，再除以86400换算成天，这样时分秒的差异也能体现到小数部分。注意Oracle对月末的判断只看日期部分不看时间，所以这里判断时用的是date1::date。

3.4 使用方式与简单验证

函数建好后，直接像Oracle里那样用：

SELECT months_between(DATE '2023-03-20', DATE '2023-01-15'); -- 返回 2.16129032258064516129032258064516129032 SELECT months_between(DATE '2023-02-28', DATE '2023-01-31'); -- 返回 1 SELECT months_between(DATE '2024-02-29', DATE '2023-02-28'); -- 返回 12 SELECT months_between(TIMESTAMP '2023-03-20 12:00:00', TIMESTAMP '2023-01-15 06:00:00'); -- 返回约 2.17562724014336917562724014336917562724

前两条分别验证了普通场景和月末场景，我自己在项目里就是用这组用例和Oracle逐一比对的。

4. 测试用例与边界验证

4.1 建立Oracle对照测试矩阵

函数写完不是终点，必须和Oracle的真实输出做对照。我在迁移项目里整理过一张测试矩阵，覆盖普通日期、跨年、闰年、月末、反向日期等场景。这里列几个代表性用例：

我自己实际跑过Oracle 11g和PostgreSQL 14的对照，输出完全一致。少数场景小数点后位数会受numeric精度影响，但业务上一般取2~4位小数，没有实际差异。

4.2 容易被忽略的边界情况

测试中最容易翻车的是这几个位置：

• 反向日期：date1早于date2时，整数部分可能为负，小数部分也可能为负，但Oracle的规则是“整体按带符号处理”，不能简单取绝对值再拼。我的函数直接相减天然支持，但如果实现时绕了弯路先把月份大的放前面再取负，就会出问题。
• 月份天数差异：1月31日到2月28日这种月末场景，靠朴素公式算出来是0.9032，加了月末判断后才是1。
• 跨年加月末：2024年2月29日到2023年2月28日，涉及闰年月末，也必须返回整数12。测试矩阵里如果漏掉这种组合，上线后一旦遇到就会非常被动。
• 时分秒导致的尾数差异：Oracle的date类型本身是带时间的，PostgreSQL的date类型不带。如果原系统大量使用带时间的日期值，建议统一走timestamp版本函数。

4.3 我在实际项目中发现的额外问题

还有一点经验：即使函数本身复刻正确，应用层的日期传入方式也可能改变结果。比如Java里通过JDBC传java.sql.Date，只保留日期部分；但从Oracle迁过来的代码有的用了java.util.Date，带时分秒。迁移后如果不做检查，同样的业务逻辑可能因为传参类型不同而产生微小偏差，虽然大多时候无关紧要，但涉及资金、账龄时一定要逐条核对。

所以我建议在迁移测试阶段，把核心报表涉及的日期字段都跑一遍“Oracle旧结果 vs PostgreSQL新结果”的比对脚本，不要只测函数本身，还要测业务SQL嵌套后的最终输出。

5. 常见问题与避坑指南

5.1 为什么函数标记要加IMMUTABLE

很多从Oracle转过来的同学写PostgreSQL函数时容易忽略IMMUTABLE，只写LANGUAGE plpgsql。这个标记直接影响查询性能：PostgreSQL的优化器会把IMMUTABLE函数的结果当成常量处理，在索引计算、分区裁剪、物化视图刷新时直接复用，而不会逐行执行函数开销。

months_between的输入输出完全由参数决定，不依赖会话状态、不查表、不取当前时间，所以必须标记为IMMUTABLE。如果标成STABLE或默认的VOLATILE，查询计划可能退化成逐行扫描，大表统计查询的性能差距非常明显。

5.2 时分秒到底要不要处理

如果你的业务只传date类型的参数，基础版完全够用。但很多老的Oracle库用的是DATE类型，本身包含时分秒，迁移到PostgreSQL后如果字段类型映射成timestamp，就一定要用支持timestamp的重载版本。否则Oracle那边算出2.167741935，PostgreSQL这边算出2.161290323，核对报表时差了几个小数位，排查起来非常浪费时间。

5.3 大批量调用时的性能优化建议

函数本身逻辑不重，但如果在几百万行的查询里直接调，还是有优化空间。我的做法是：

• 确保字段类型和函数参数类型一致，避免隐式转换带来的表达式索引失效。
• 如果固定在某几个日期列上反复计算，可以考虑建表达式索引：CREATE INDEX idx_months_between ON t (months_between(end_date, start_date));
• 函数内避免使用子查询和临时表，只做标量计算，把函数體量控制在最小。

实测下来，一个百万行的分页统计接口，从最初每条计算2毫秒左右优化到0.3毫秒以内，主要就是靠IMMUTABLE标记和表达式索引。

5.4 一个让我踩过坑的案例：ORDER BY里直接用函数

我早期有个查询在ORDER BY里直接写了months_between(a, b)，数据量一大就慢。后来看执行计划，排序操作没法利用索引，只能逐行算函数值再排序。优化方案是把计算结果落成一个冗余字段，或者在查询里先算好再排序，避免排序阶段重复计算。

还有一次是函数的schema问题：迁移时把函数建在public下，但业务用户连的是业务schema，搜索路径没配好，结果一直提示“function months_between does not exist”。查了半天才意识到是search_path的问题，加上schema前缀或调整ALTER ROLE ... SET search_path后解决。

6. 最后补充一点实用经验

我个人在实际项目里的做法是：不管是新建PostgreSQL数据库还是做迁移，都会把这类通用日期函数统一整理到一个公共schema里，比如common.months_between，然后通过search_path让业务库默认可见。这样既不影响原有代码结构，又方便后续统一运维和扩展。再配合前面列的测试矩阵，把用例写成自动化回归脚本，每换一次数据库版本就自动跑一遍，能省下大量排查时间。

你如果正被Oracle到PostgreSQL的语法差异困扰，强烈建议不只盯着MONTHS_BETWEEN，把常用的日期、字符串、序列生成函数都过一遍，提前做好等价函数映射表。这活儿看着琐碎，做完了后面迁移才会顺。