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

刺骨的言语ヽ痛彻心扉 2021-09-24 20:02 409阅读 0赞

### 文章目录 ###

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

给出开始时间和增/减的时间，求出跳过休息日的结束时间。  
例：  
已知2020年9、10月日历  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70_pic_center]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70_pic_center 1]

若开始时间为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获取法定节假日][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
    BEGIN
    	
        DECLARE _f_time datetime DEFAULT _p_start_time;
    	
    	if _p_add_day > 9999 or _p_add_day < -9999 then
    	  return 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 dat
    	  into _f_time
    	  from (
    	select t1.dat,
    		   @cot:=case when t1.state != 1 and @cot < abs(_p_add_day) then @cot+1 else @cot end flag
    	  from (
    	select t1.dat,
    		   case when t2.state is not null then t2.state
    			 else
    			   case when date_format(t1.dat, '%w') in (6, 0)
    				 then 1
    				 else 2
    			   end
    		   end state
    	  from (
    	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) t2
    	  order 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
    	) t1
    	 where 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')) state
      from (
            select trunc(sysdate + level - 1, 'dd') dat--sysdate改为增加天数
              from dual connect by level <= greatest(2 + 1000, floor(2 * 7 / 5))--需要一个足够大的数，至少要比结束时间大
            ) t1, 
           my_playday t2
     where t1.dat = t2.acct_day(+)
    )
    , tab2 as (
    select t1.*,
           row_number() over(order by t1.dat) rn
      from tab1 t1
     where t1.state = 2
    )
    select t1.dat
      from tab2 t1
     where t1.rn = 2--增加2天
    ;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70_pic_center]: /images/20210923/6304e1f4855147f2a75b6958fe6de18a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20210923/50d23388aaee4cfda9c90579cb01753c.png
[java]: https://blog.csdn.net/nayi_224/article/details/109068470