计算超时或工作日 排除周末

最近做一个需求，计算一个工作人员处理一个流程需要花费的时间，排除周末处理时间，只算工作日处理时长，用来比对是否超时。以下用mysql函数的方日期式计算两个之间工作日的时间差：

BEGIN

declare minutes INT;

declare  flag VARCHAR(2);

declare  days INT;

declare  weeks INT;

declare  week_day INT;

declare  result INT;

declare  startime datetime;

declare  endtime datetime;

declare  begweek INT;

declare endweek INT;

set week_day = date_format(startdate,'%w');

if week_day = 0 then 

    set startime = str_to_date(date_format(date_add(startdate, INTERVAL 1 DAY),'%Y-%m-%d'),'%Y-%m-%d %h:%i:%s');

elseif week_day = 6 then

    set startime = str_to_date(date_format(date_add(startdate, INTERVAL 2 DAY),'%Y-%m-%d'),'%Y-%m-%d %h:%i:%s');

else

    set startime = startdate;

end if;

set week_day = date_format(enddate,'%w');

if week_day = 0 then

    set endtime = str_to_date(date_format(date_add(enddate, INTERVAL -1 DAY),'%Y-%m-%d'),'%Y-%m-%d %h:%i:%s');

elseif week_day = 6 then

  set endtime = str_to_date(date_format(date_add(enddate, INTERVAL 0 DAY),'%Y-%m-%d'),'%Y-%m-%d %h:%i:%s');

else

  endtime = enddate;

end if;

set minutes = timestrampdiff(minute,startime,endtime);

set days = timestrampdiff(day,startime,endtime);

set result = 0;

set begweek = date_format(startime,'%w');

set endweek = date_format(endtime,'%w'); 

#开始时间周小于结束时间周  有两种情况：1-开始时间大于结束时间 2-开始时间小于等于结束时间

if begweek < endweek then

  if startime > endtime then

    set result =0;

  else

    set weeks = floor(days/7);

    set result = minutes - 2*weeks*24*60;

  end if;

else if begweek > endweek  then

  set weeks = floor(days/7)+1;

  set result = minutes - 2*weeks*24*60;

elseif begweek = endweek then

  if days/7 >=0 then

     set weeks = floor(days/7);

    set result = minutes - 2*weeks*24*60;

  else

    set result = minutes;

  end if;

end if;

if result > longtime then

  set result = result - longtime;

else

  set result = 0;

end if;

RETURN result;

END

以下图片是函数类型 、参数、返回类型