区间炸裂函数
该自定义函数功能类似Hive中explode函数,将给定的区间按照月份炸裂,返回区间所在的月初和月末。
--传入参数
start_date:'2023-07-17'
end_date:'2023-09-03'
--返回值:
| begin_of_month | end_of_month |
|----------------|--------------|
| 2023-07-01 | 2023-07-31 |
| 2023-08-01 | 2023-08-31 |
| 2023-09-01 | 2023-09-30 |
CREATE OR REPLACE FUNCTION prd_dws.period_split_into_months(start_date date, end_date date) RETURNS SETOF text AS
$BODY$
--功能描述: 传入时间区间的开始日期和结束日期,返回区间包含的全部月初与月末(区间炸裂函数)
--参数说明:
-- start_date date:区间开始日期
-- end_date date:区间结束日期
--返回值:
-- begin_of_month date: 月初;end_of_month date:月末
declare
i_date date; --循环条件
begin_of_month date; --月初
end_of_month date; --月末
r text; --结果集
begin
i_date := start_date;
if(i_date <= end_date) then
while date_trunc('month', i_date) <= date_trunc('month', end_date) loop
--月初
begin_of_month := date_trunc('month', i_date);
--月末
end_of_month := date_trunc('month', i_date + interval '1 month') - interval '1 day';
--结果集
r := begin_of_month||','||end_of_month;
--出口
i_date := date_trunc('month', i_date) + interval '1 month';
return next r;
end loop;
elsif(i_date > end_date) then
--直接返回start_date所在月份
--月初
begin_of_month := date_trunc('month', i_date);
--月末
end_of_month := date_trunc('month', i_date + interval '1 month') - interval '1 day';
--结果集
r := begin_of_month||','||end_of_month;
return next r;
end if;
end
$BODY$ LANGUAGE plpgsql VOLATILE EXECUTE ON ANY COST 100.0 ROWS 1000.0;
日期计算函数(加减月份)
对日期进行加减月份计算时,GP原生的日期计算方式(‘2023-04-30’::date - interval ‘1 month’)结果是’2023-03-30’,并非预期中的’2023-03-31’。需要自定义函数解决该问题。
--传入参数
in_timestamp:'2023-04-30 12:00:00'
cnt:-1
--返回值:
res:'2023-03-3112:00:00 '
CREATE OR REPLACE FUNCTION public.add_months(in_timestamp timestamp without time zone, cnt integer) RETURNS timestamp without time zone AS
$BODY$
--功能说明:月份计算函数,解决in_timestamp为月末最后一天月份加减不准确的问题
--参数说明:
-- in_timestamp timestamp without time zone:传入日期
-- cnt int:需要加减的月份
--返回值:
-- res timestamp without time zone:计算后的日期
declare
months interval := (cnt || 'month');
d1 date := date(date_trunc('month',in_timestamp) + interval '1 month' - interval '1 day');
d2 date := date(in_timestamp);
res timestamp;
begin
select
case
when d1=d2
then date_trunc('month',in_timestamp + months + interval '1 month')::date - interval '1 day' + in_timestamp::time
else in_timestamp + months
end into res;
return res::timestamp without time zone;
end;
$BODY$ LANGUAGE plpgsql VOLATILE EXECUTE ON ANY COST 100.0;