sql跳过休息日增加/减少时间

文章目录

• • • mysql7使用函数解决方案
    • • 函数
    • oracle

若开始时间为2020-10-09，增加1天，则结束时间应为2020-10-09
若开始时间为2020-10-09，增加2天，则结束时间应为2020-10-10
若开始时间为2020-10-01，增加1天，则结束时间应为2020-10-09
若开始时间为2020-10-01，减少2天，则结束时间应为2020-09-29

需要注意的是这种需求是不可逆的，因为节假日可以作为开始时间，但是不可作为结束时间。

mysql7使用函数解决方案

创建一个表来记录节假日信息

CREATE TABLE `my_playday`  (`acct_day` date NOT NULL,`state` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '1节假日 2周末调休转工作日 ',PRIMARY KEY (`acct_day`) USING BTREE,INDEX `index_my_playday`(`acct_day`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

节假日获取方法见上一篇文章
java获取法定节假日

创建一个足够大的表，一次存入数字1、2、3、4、5…

create table sys_big_num(num int
);

函数

CREATE DEFINER=`skip-grants user`@`skip-grants host` FUNCTION `f_date_add_playday`(`_p_start_time` datetime,`_p_add_day` integer) RETURNS datetime
BEGINDECLARE _f_time datetime DEFAULT _p_start_time;if _p_add_day > 9999 or _p_add_day < -9999 thenreturn date_add(_p_start_time, INTERVAL floor(_p_add_day * (5 / 7)) day);#时间跨度过大不考虑法定节假日end if;if abs(_p_add_day) = 0 then return _p_start_time;end if;select case when _p_add_day > 0 then min(t1.dat) else max(t1.dat) end datinto _f_timefrom (select t1.dat,@cot:=case when t1.state != 1 and @cot < abs(_p_add_day) then @cot+1 else @cot end flagfrom (select t1.dat,case when t2.state is not null then t2.stateelsecase when date_format(t1.dat, '%w') in (6, 0)then 1else 2endend statefrom (select date_format(date_add(_p_start_time, INTERVAL (t1.num - 1) * case when _p_add_day > 0 then 1 else -1 end day), '%Y-%m-%d') dat from sys_big_num t1 where t1.num < least(greatest(abs(_p_add_day) * 1.5, abs(_p_add_day) + 1000), 9999)#边界值) t1 left join my_playday t2 on t1.dat = t2.acct_day) t1, (select @cot:=0) t2order by case when _p_add_day > 0 then t1.dat else null end, case when _p_add_day < 0 then t1.dat else null end desc) t1where t1.flag = abs(_p_add_day);	RETURN _f_time;
END

调用方法

select f_date_add_playday('2020-10-1', 2);

由于函数内只有一条查询语句，所以也可以拆出来脱离函数使用。

oracle

上文中使用的是累加的思想，有点绕，这回用直等写一个，代码要简单很多。

代码很简单直接发sql不写函数了。
虽然也可以把减少天数整合到一条语句中，但是看着很乱，只写了增加天数的。

with tab1 as (
select t1.dat,nvl(t2.state, decode(to_char(t1.dat, 'd'), '1', '1', '7', '1', '2')) statefrom (select trunc(sysdate + level - 1, 'dd') dat--sysdate改为增加天数from dual connect by level <= greatest(2 + 1000, floor(2 * 7 / 5))--需要一个足够大的数，至少要比结束时间大) t1, my_playday t2where t1.dat = t2.acct_day(+)
)
, tab2 as (
select t1.*,row_number() over(order by t1.dat) rnfrom tab1 t1where t1.state = 2
)
select t1.datfrom tab2 t1where t1.rn = 2--增加2天
;

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！