GaussDB(DWS)迁移 - oracle兼容 -- months_between

CREATE OR REPLACE FUNCTION public.months_between(pg_catalog.date, pg_catalog.date)
 RETURNS numeric
 LANGUAGE sql
 STABLE NOT FENCED SHIPPABLE
AS $function$
    SELECT (
        (EXTRACT(YEAR FROM $1) - EXTRACT(YEAR FROM $2)) * 12 +  -- 年
        EXTRACT(MONTH FROM $1) - EXTRACT(MONTH FROM $2) +       -- 月
        CASE WHEN EXTRACT(DAY FROM $2) = EXTRACT(DAY FROM $1) THEN 0  -- 同一天
             WHEN EXTRACT(DAY FROM $2) = EXTRACT(DAY FROM LAST_DAY($2)) AND EXTRACT(DAY FROM $1) = EXTRACT(DAY FROM LAST_DAY($1)) THEN 0 -- 都是最后一天
             ELSE (EXTRACT(DAY FROM $1) - EXTRACT(DAY FROM $2))::numeric / 31::numeric
        END
    )::numeric
$function$
;
CREATE OR REPLACE FUNCTION public.months_between(timestamp, timestamp)
 RETURNS numeric
 LANGUAGE sql
 STABLE NOT FENCED SHIPPABLE
AS $function$
    SELECT (
        (EXTRACT(YEAR FROM $1) - EXTRACT(YEAR FROM $2)) * 12 +  -- 年
        EXTRACT(MONTH FROM $1) - EXTRACT(MONTH FROM $2) +       -- 月
        CASE WHEN EXTRACT(DAY FROM $2) = EXTRACT(DAY FROM $1) THEN 0  -- 同一天
             WHEN EXTRACT(DAY FROM $2) = EXTRACT(DAY FROM LAST_DAY($2)) AND EXTRACT(DAY FROM $1) = EXTRACT(DAY FROM LAST_DAY($1)) THEN 0 -- 都是最后一天
             ELSE (EXTRACT(DAY FROM $1) - EXTRACT(DAY FROM $2) + intervaltonum($1::time - $2::time) )::numeric / 31::numeric
        END
    )::numeric
$function$
;
CREATE OR REPLACE FUNCTION public.months_between(timestamptz, timestamptz)
 RETURNS numeric
 LANGUAGE sql
 STABLE NOT FENCED SHIPPABLE
AS $function$
    SELECT (
        (EXTRACT(YEAR FROM $1) - EXTRACT(YEAR FROM $2)) * 12 +  -- 年
        EXTRACT(MONTH FROM $1) - EXTRACT(MONTH FROM $2) +       -- 月
        CASE WHEN EXTRACT(DAY FROM $2) = EXTRACT(DAY FROM $1) THEN 0  -- 同一天
             WHEN EXTRACT(DAY FROM $2) = EXTRACT(DAY FROM LAST_DAY($2)) AND EXTRACT(DAY FROM $1) = EXTRACT(DAY FROM LAST_DAY($1)) THEN 0 -- 都是最后一天
             ELSE (EXTRACT(DAY FROM $1) - EXTRACT(DAY FROM $2) + intervaltonum($1::time - $2::time) )::numeric / 31::numeric
        END
    )::numeric
$function$
;