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$
;