mysql计算两个日期间实效|参数传入每天上班时间，并剔除周末

项目中遇到需求就是，需要统计案子进入我们项目的时间，然后我们完成回传给对方的时间，会设置一个实效

所以借用mysql的存储过程计算出一个项目是否超时：

# 查询时间段内的实效函数(剔除周末与非工作时间)
# 参数解释
# _st: 开始时间
# _ed: 结束时间
# _hour1: 工作时间开始小时
# _hour2: 工作时间结束小时
# _minute1: 工作时间开始分钟
# _minute2: 工作时间结束分钟
# 返回案件总分钟数

DELIMITER $$
CREATE FUNCTION work_minute_sum(_st DATETIME, _ed DATETIME, _hour1 INT, _hour2 INT, _minute1 INT, _minute2 INT) RETURNS INTEGER
BEGIN
    DECLARE _one_week_minute INT DEFAULT 10080;
    DECLARE _one_day_work_minute INT DEFAULT (_hour2 - _hour1) * 60 - _minute1 + _minute2;
    DECLARE _st_hour INT DEFAULT DATE_FORMAT(_st, '%H');
    DECLARE _ed_hour INT DEFAULT DATE_FORMAT(_ed, '%H');
    DECLARE _st_minute INT DEFAULT DATE_FORMAT(_st, '%i');
    DECLARE _ed_minute INT DEFAULT DATE_FORMAT(_ed, '%i');
    DECLARE _st_week INT DEFAULT DATE_FORMAT(_st, '%w');
    DECLARE _ed_week INT DEFAULT DATE_FORMAT(_ed, '%w');
    
    DECLARE _diffminute INT;
    DECLARE _che DATETIME;
    DECLARE _flag INT DEFAULT 1;
    DECLARE _week INT;
    DECLARE _hour_div INT;
    DECLARE _count_week INT DEFAULT 0;
    DECLARE _work_minute INT DEFAULT 0;
    
    IF ((_hour1 > _hour2) || (_hour1 = _hour2 && _minute1 >= _minute2)) THEN
        RETURN 0;
    END IF;
    IF _st > _ed THEN 
        SET _che = _st;
        SET _st = _ed;
        SET _ed = _che;
        SET _flag = -1;
    END IF;

    SET _diffminute = TIMESTAMPDIFF(MINUTE, _st, _ed);
    SET _count_week = _diffminute / _one_week_minute;
    SET _work_minute = _count_week * 5 * _one_day_work_minute;
    
    SET _st = DATE_ADD(_st, INTERVAL (_count_week * 7) DAY);

    IF (_st_week = 0 || _st_week = 6) THEN
        SET _work_minute = _work_minute;
    ELSEIF ((_st_hour > _hour2) || (_st_hour = _hour2 && _st_minute >= _minute2)) THEN
        SET _work_minute = _work_minute - _one_day_work_minute;
	ELSEIF (_st_hour > _hour1) || (_st_hour = _hour1 && _st_minute > _minute1) THEN
        SET _hour_div = _st_hour - _hour1;
        SET _work_minute = _work_minute - (_hour_div * 60) - _st_minute + _minute1;
    END IF;
    
    IF _ed_week = 0 || _ed_week = 6 || _ed_hour < _st_hour || (_ed_hour = _st_hour && _ed_minute <= _st_minute) THEN
        SET _work_minute = _work_minute;
    ELSEIF (_ed_hour < _hour1) || (_ed_hour = _hour1 && _ed_minute < _minute1) THEN
        SET _work_minute = _work_minute - _one_day_work_minute;
    ELSEIF (_ed_hour < _hour2) || (_ed_hour = _hour2 && _ed_minute < _minute2) THEN
		SET _hour_div = _hour2 - _ed_hour;
        SET _work_minute = _work_minute - (_hour_div * 60) + _ed_minute - _minute2;
    END IF;
    
    loop_cal:LOOP
        SET _week = DATE_FORMAT(_st, '%w');
        IF _st >= _ed THEN
            LEAVE loop_cal;
        END IF;
        IF _week != 0 && _week != 6 THEN
		    SET _work_minute = _work_minute + _one_day_work_minute;
        END IF;
        SET _st = DATE_ADD(_st, INTERVAL 1 DAY);
    END LOOP;
    
    RETURN _work_minute * _flag;
END $$
DELIMITER ;

调用方法
#例：计算工作日 09:00 ~ 18:00的实效：
select work_minute_sum(‘2019-03-03 00:00:00’, ‘2019-03-13 22:00:00’, 9, 18, 0, 0) ;