mysql自定义函数计算时间段内的工作日(支持跨年)

① 同一年的情况下计算工作日函数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

CREATE DEFINER=`root`@`%` FUNCTION `WORKDAYSONEYEAR`(`datefrom` datetime,`dateto` datetime) RETURNS int(20)     NO SQL BEGIN     declare days int default 1;     # 如果起始时间大于结束时间或者日期跨年那么直接返回-1，表示不支持     if (datefrom > dateto  or year(datefrom) != year(dateto)) then        return -1;     end if;           set days =        case        # 同一周的情况：计算时间间隔再减去周六周日的天数        # 每周开始时间为星期日，1是星期日  7是星期六        when week(dateto)-week(datefrom) = 0 then             dayofweek(dateto) - dayofweek(datefrom) + 1           - case             when (dayofweek(datefrom) > 1 and dayofweek(dateto) < 7) then 0             when (dayofweek(datefrom) = 1 and dayofweek(dateto) =7) then 2             else 1             end        #不是同一周的情况：间隔周数 * 5 加上同一周的工作日          else (week(dateto)-week(datefrom)-1) * 5           + case             when dayofweek(datefrom) = 1 then 5             when dayofweek(datefrom) = 7 then 0             else 7 - dayofweek(datefrom)             end           + case             when dayofweek(dateto) = 1 then 0             when dayofweek(dateto) = 7 then 5             else dayofweek(dateto) - 1             end        end;                       return days; end

② 计算跨一年的情况

本质还是使用上面的函数。 比如计算2019-12-23到2020-01-10的工作日，可以使用上面的函数分别计算2019-12-25到20 9-12-31和2020-01-01到2020-01-10的工作日，取两者之和即可。　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

CREATE DEFINER=`root`@`%` FUNCTION `WORKDAYSTWOYEARS`(`startdate` datetime,`enddate` datetime) RETURNS int(20) BEGIN     #起始时间大于结束时间，直接返回-1，表示不支持     if (startdate > enddate) then        return -1;     #同一年的情况下，直接使用上面的WORKDAYSONEYEAR()函数计算     ELSEIF (year(startdate) = year(enddate)) then             set @days = WORKDAYSONEYEAR(startdate,enddate);             return @days;     #年份相差一年，分两段进行处理         ELSEIF (year(startdate) < year(enddate)) then         set @yearofstartdate = year(startdate);         set @yearofenddate = year(enddate);         set @lastdayofstartdate = CONCAT(@yearofstartdate,'-12-31');         set @intervelone = WORKDAYSONEYEAR(startdate,@lastdayofstartdate);         set @days = @intervelone;         set @firstdayofenddate = CONCAT(@yearofenddate,'-01-01');         set @interveltwo = WORKDAYSONEYEAR(@firstdayofenddate,enddate);         set @days = @intervelone + @interveltwo;     end if; return @days; end

测试：

?

1	select WORKDAYSTWOYEARS('2019-12-15','2020-01-05');