GaussDB(DWS)迁移 - oracle兼容 -- months_between
【摘要】 适配Oralce函数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$
;
想了解GuassDB(DWS)更多信息,欢迎微信搜索“GaussDB DWS”关注微信公众号,和您分享最新最全的PB级数仓黑科技,后台还可获取众多学习资料哦~
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)