Postgresql中，计算两个日期月份差值或年月日，实现Oracle中months_between、add_months的效果

Oracle中存在months_between、add_months函数，用作计算年龄等，例如计算某个人的年龄：岁（age）-月（monthss）-天(days)

SELECT rowid,
       a.fidate,
       a.fbirth,
       trunc(months_between(fidate, fbirth) / 12) age,
       trunc(mod(months_between(fidate, fbirth), 12)) monthss,
       trunc(fidate -
             add_months(fbirth, trunc(months_between(fidate, fbirth)))) days
  from t_noentryquery a;

Oracle中调用，如下图：

为兼容Postgresql计算年龄等，以此来创建函数months_between

--入参为date类型
CREATE OR REPLACE FUNCTION months_between(date1 date, date2 date)
RETURNS "pg_catalog"."int4" AS $BODY$
DECLARE
v_res int;
begin
SELECT 12*EXTRACT(YEAR from age(date1,  date2))+EXTRACT(MONTH from age(date1, date2)) into v_res from dual;
RETURN v_res;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

--或者

--入参为 timestamp without time zone类型
CREATE OR REPLACE FUNCTION months_between(
	date1 timestamp without time zone,
	date2 timestamp without time zone)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
v_res int;
begin
SELECT 12*EXTRACT(YEAR from age(date1::date,  date2::date))+EXTRACT(MONTH from age(date1::date, date2::date)) into v_res from dual;
RETURN v_res;
end;
$BODY$;

为兼容Postgresql计算年龄等，以此来创建函数add_months

--入参为timestamp without time zone,numeric类型
create or replace function add_months(timestamp without time zone, numeric) returns timestamp as $$  
declare  
  i interval := ($2 || 'month');  
  d1 date := date(to_timestamp($1::text,'yyyy-mm') + interval '1 month' - interval '1 day');  
  d2 date := date($1);  
  res timestamp;  
begin  
  select case when d1=d2 then ((to_char($1+i+interval '1 month', 'yyyy-mm')||'-01')::date - 1) + $1::time else $1+i end into res;  
  return res;  
end;  
$$ language plpgsql strict; 

--或者

--入参为timestamp,numeric类型
create or replace function add_months(timestamp, numeric) returns timestamp as $$  
declare  
  i interval := ($2 || 'month');  
  d1 date := date(to_timestamp($1::text,'yyyy-mm') + interval '1 month' - interval '1 day');  
  d2 date := date($1);  
  res timestamp;  
begin  
  select case when d1=d2 then ((to_char($1+i+interval '1 month', 'yyyy-mm')||'-01')::date - 1) + $1::time else $1+i end into res;  
  return res;  
end;  
$$ language plpgsql strict; 

Postgresql中调用:

SELECT 
       a.fidate,
       a.fbirth,
       trunc(months_between(fidate, fbirth) / 12) age,
       trunc(mod(months_between(fidate, fbirth), 12)) monthss,
       trunc(fidate::date - add_months(fbirth,
         cast(trunc(months_between(fidate, fbirth)) as numeric))::date) days
  from t_noentryquery a;

如下图：

当然也可以用Postgresql中自带的函数age()，更加方便快捷

select age(fidate ::date, fbirth ::date), a.fidate, a.fbirth
  from T_NOENTRYQUERY a
 where a.fidate is not null
   and a.fbirth is not null

如下图：