计算2个日期之间的差时

create table caltab (
  n_date int(11) not null comment ‘无格式日期‘,
  y_date date not null comment ‘有格式日期‘,
  y_year int(11) not null comment ‘年度‘,
  y_month varchar(9) collate utf8mb4_0900_as_cs not null comment ‘月度‘,
  y_day int(11) not null comment ‘天‘,
  y_quarter int(11) not null comment ‘季度‘,
  y_week int(11) not null comment ‘周‘,
  y_num varchar(9) collate utf8mb4_0900_as_cs not null comment ‘每周星期几‘,
  holiday_flag tinyint(1) default ‘0‘ comment ‘是否节假日‘,
  working_flag tinyint(1) default ‘0‘ comment ‘是否工作日‘,
  remark varchar(50) character set utf8mb4 collate utf8mb4_0900_as_cs default null comment ‘补充说明‘,
  primary key (n_date),
  unique key td_ymd_idx (y_date,y_month,y_day),
  unique key td_y_date_idx (y_date),
  key weekend_flag (working_flag),
  key holiday_flag (holiday_flag),
  key y_year (y_year),
  key y_month (y_month),
  key y_day (y_day)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_as_cs

DELIMITER $$
USE devops$$
DROP PROCEDURE IF EXISTS `proc_calender_day`$$
CREATE DEFINER=`coreuser`@`%` PROCEDURE `proc_calender_day`(IN start_date DATE,IN stop_date DATE)
BEGIN
## ==================================================================================
##    让读书成为一种生活方式。就像吃喝拉撒每天必须要干的事，
## 终有一天你的举止、言谈、气质会不一样。 
##                                        —- async 
##
## Created Date: Thursday, 2020-05-09, 8:46:18 am
## copyright (c):    SZWW Tech. LTD. 
## Engineer:   async 
## Module Name:   
## Revision:   v0.01 
## Description:
##   
## Revision History : 
## Revision     editor         date         Description         
## v0.01        async          2020-01-16   File Created
## ==================================================================================
    DECLARE currentdate DATE;
    SET currentdate = start_date;
    WHILE currentdate < stop_date DO
    INSERT INTO caltab VALUES (
       YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
       currentdate,
       YEAR(currentdate), -- 年度
       DATE_FORMAT(currentdate,‘%m‘), -- 月度
       DAY(currentdate), -- 当月第x天
       QUARTER(currentdate),-- 季度
       WEEKOFYEAR(currentdate), -- 第x周
       case when DATE_FORMAT(currentdate,‘%w‘)=0 then ‘日‘
            when DATE_FORMAT(currentdate,‘%w‘)=1 then ‘一‘
            when DATE_FORMAT(currentdate,‘%w‘)=2 then ‘二‘
            when DATE_FORMAT(currentdate,‘%w‘)=3 then ‘三‘
            when DATE_FORMAT(currentdate,‘%w‘)=4 then ‘四‘
            when DATE_FORMAT(currentdate,‘%w‘)=5 then ‘五‘
            when DATE_FORMAT(currentdate,‘%w‘)=6 then ‘六‘ end, -- 星期几
       CASE
         WHEN DAY(currentdate)=1 AND MONTH(currentdate)=1 THEN ‘1‘ -- 元旦
         WHEN DAY(currentdate) between 24 and 30 AND MONTH(currentdate)=1 THEN ‘1‘ -- 春节(1月24日至30日放假调休，共7天。1月19日（星期日）、2月1日（星期六）上班)
         WHEN DAY(currentdate) between 4 and 6 AND MONTH(currentdate)=4 THEN ‘1‘ -- 清明节 （4月4日至6日放假调休）
         WHEN DAY(currentdate) between 1 and 5 AND MONTH(currentdate)=5 THEN ‘1‘ -- 劳动节（5月1日至5日放假调休，共5天。4月26日（星期日）、5月9日（星期六）上班。）
         WHEN DAY(currentdate) between 25 and 27 AND MONTH(currentdate)=6 THEN ‘1‘ -- 端午节（6月25日至27日放假调休，共3天。6月28日（星期日）上班）
     WHEN DAY(currentdate) between 1 and 8 AND MONTH(currentdate)=10 THEN ‘1‘ -- 国庆，中秋节（国庆节、中秋节：10月1日至8日放假调休，共8天。9月27日（星期日）、10月10日（星期六）上班）
         ELSE ‘0‘ END,
         CASE DAYOFWEEK(currentdate) WHEN 2 THEN ‘1‘ WHEN 3 THEN ‘1‘ WHEN 4 THEN ‘1‘ WHEN 5 THEN ‘1‘ WHEN 6 THEN ‘1‘ ELSE ‘0‘ END, -- 是否工作日
         NULL);
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
    --  补班特殊处理
    update caltab set working_flag=‘1‘,remark=‘#补班‘ where y_date in(‘2020-01-19‘,‘2020-02-01‘,‘2020-04-26‘,‘2020-05-09‘,‘2020-06-28‘,‘2020-09-27‘,‘2020-10-10‘);
END$$
DELIMITER ;

DELIMITER $$
USE `devops`$$
DROP FUNCTION IF EXISTS `get_wk_hour`$$
CREATE DEFINER=`coreuser`@`%` FUNCTION `get_wk_hour`(i_time datetime,e_time datetime) RETURNS int(11)
begin
declare v_s_time datetime;
declare v_e_time datetime;
declare v_hours int;
declare h_hours int;

set v_s_time:=i_time;
set v_e_time:=e_time;

-- 如果开始时间>结束时间，则置换位置
if v_s_time>v_e_time
then
select v_s_time,v_e_time into v_e_time,v_s_time from dual;
end if;
-- 判断开始日期是否<当天上午9点
if hour(v_s_time)<9
then
select  concat(date_format(v_s_time,‘%Y-%m-%d‘),‘ 09:00:00‘) into v_s_time from dual;
elseif hour(v_s_time)>18
then
select  concat(date_add(date_format(v_s_time,‘%Y-%m-%d‘),interval 1 day),‘ 09:00:00‘) into v_s_time from dual;
end if;
-- 判断结束日期是否<当天上午9点
if hour(v_e_time)<9
then
select  concat(date_add(date_format(v_e_time,‘%Y-%m-%d‘),interval -1 day),‘ 18:00:00‘) into v_e_time from dual;
elseif hour(v_e_time)>18
then
select  concat(date_add(date_format(v_e_time,‘%Y-%m-%d‘),interval 0 day),‘ 18:00:00‘) into v_e_time from dual;
end if;

-- 找出开始日期与结束日期之间的工时时长(单位:hour),排除周末,排除法定节假日
select count(1) into v_hours from caltab ma where holiday_flag=1
and ma.y_date between date_format(v_s_time,‘%Y-%m-%d‘) and date_format(v_e_time,‘%Y-%m-%d‘);

-- 判断开始结束日期是否为同一天
select (case when date_format(v_s_time,‘%Y-%m-%d‘)=date_format(v_e_time,‘%Y-%m-%d‘) and timestampdiff(hour,v_s_time,v_e_time)<8
       then  timestampdiff(hour,v_s_time,v_e_time)
            when  date_format(v_s_time,‘%Y-%m-%d‘)=date_format(v_e_time,‘%Y-%m-%d‘) and timestampdiff(hour,v_s_time,v_e_time)>=8
       then 8
-- 开始结束日期不在同一天,开始日期在节假日,结束日期在节假日
            when datediff(v_s_time,v_e_time) <> 0
            then (datediff(v_e_time,v_s_time)+1-v_hours)*8
           -- timestampdiff(hour,v_s_time,concat(date_format(v_s_time,‘%Y-%m-%d‘),‘ 18:00:00‘))
end ) into h_hours from dual;

return h_hours;
end$$

DELIMITER ;