作者 | 星空XXL
来源 | 今日头条
最近小编在项目中遇到需要查询某个工单还有多少剩余处理时间的需求，并且要按照工作时的计算方式，对剩余处理时间按升序排列，如果是对查询出来的工单进行剩余时间计算，那么在程序中就能够完成，但偏偏要求需要进行排序，这可就难了，因为数据表内记录超过百万，放在程序中处理肯定会溢出，最终还是选择加个mysql函数，让数据库帮忙处理，但找遍各大论坛也没有找到在mysql中计算工作时的方法，无奈只能自己来写个，在这里分享给大家!
不说废话，直接上代码!
这里要说明一下函数中使用的 frame_workingday 这个表，这是一张日历表，记录了哪天是工作日，跟百度搜索的万年历一样的
drop function if exists getworkminute;
create function getworkminute(starttime datetime, endtime datetime) returns integer
begin
-- 声明变量 --
-- 最终结果
declare interval_minute integer default 0;
-- 开始时间是否工作日
declare startiswork integer default 0;
-- 结束时间是否工作日
declare endiswork integer default 0;
-- 开始时间与结束时间之间的工作日天数，包含自身
declare workdaynum integer default 0;
-- 定义上午、下午 上下班时间 --
set @monworkstart = '08:00:00';
set @monworkend = '12:00:00';
set @noonworkstart = '14:00:00';
set @noonworkend = '18:00:00';
set @startdate = date_format(starttime,'%Y-%m-%d');
set @enddate = date_format(endtime,'%Y-%m-%d');
set @timestart = date_format(starttime, '%H:%i:%s');
set @timeend = date_format(endtime, '%H:%i:%s');
-- 查询
select count((@startdate = wdate and isworkingday = 1) or null)
, count((@enddate = wdate and isworkingday = 1) or null)
, count(isworkingday = 1 or null)
into startiswork,endiswork,workdaynum
from frame_workingday
where wdate between @startdate and @enddate;
-- 如果开始和结束时间是同一天，且是工作日
if @startdate = @enddate and startiswork = 1 and endiswork = 1 then
set workdaynum = 0;
-- 开始时间小于上午上班时间
if @timestart < @monworkstart then
-- 结束时间处于上午工作时间，计算 上午上班时间与结束时间间隔
if @timeend > @monworkstart and @timeend < @monworkend then
set interval_minute = interval_minute + timestampdiff(minute, concat(@startdate,' ',@monworkstart), concat(@startdate,' ', @timeend));
-- 结束时间处于午休时间，计算 半天
elseif @timeend >= @monworkend and @timeend <= @noonworkstart then
set interval_minute = interval_minute + 240;
-- 结束时间处于下午工作时间，计算 半天+下午上班时间与结束时间间隔
elseif @timeend >= @noonworkstart and @timeend <= @noonworkend then
set interval_minute = interval_minute + 240 + timestampdiff(minute, concat(@startdate,' ',@noonworkstart), concat(@startdate,' ', @timeend));
-- 结束时间大于下午下班时间，计算 一天
elseif @timeend >= @noonworkend then
set interval_minute = interval_minute + 480;
end if;
-- 开始时间小于上午下班时间
elseif @timestart < @monworkend then
-- 结束时间小于上午下班时间，计算 开始时间与结束时间间隔
if @timeend < @monworkend then
set interval_minute = interval_minute + timestampdiff(minute, starttime, concat(@startdate,' ', @timeend));
-- 结束时间是午休时间，计算 开始时间与上午下班时间间隔
elseif @timeend >= @monworkend and @timeend <= @noonworkstart then
set interval_minute = interval_minute + timestampdiff(minute, starttime, concat(@startdate,' ', @monworkend));
-- 结束时间在下午工作时间，计算 开始时间与上午下班时间间隔+下午上班时间与结束时间间隔
elseif @timeend >= @noonworkstart and @timeend <= @noonworkend then
set interval_minute = interval_minute + timestampdiff(minute, starttime, concat(@startdate,' ', @monworkend)) + timestampdiff(minute, concat(@startdate,' ',@noonworkstart), concat(@startdate,' ', @timeend));
-- 结束时间大于下午下班时间 计算 开始时间与上午下班时间间隔+半天
elseif @timeend>= @noonworkend then
set interval_minute = interval_minute + timestampdiff(minute, starttime, concat(@startdate,' ', @monworkend)) + 240;
end if;
-- 开始时间小于下午上班时间，即处于午休时间
elseif @timestart < @noonworkstart then
-- 结束时间小于下午上班时间，计算 0
if @timeend < @noonworkstart then
set interval_minute = interval_minute + 0;
-- 结束时间在下午工作时间，计算 下午上班时间与结束时间间隔
elseif @timeend >= @noonworkstart and @timeend <= @noonworkend then
set interval_minute = interval_minute + timestampdiff(minute, concat(@startdate,' ',@noonworkstart), concat(@startdate,' ', @timeend));
-- 结束时间大于下午下班时间 计算 半天
elseif @timeend>= @noonworkend then
set interval_minute = interval_minute + 240;
end if;
-- 开始时间小于下午下班时间
elseif @timestart < @noonworkend then
-- 结束时间小于下午下班时间，计算 开始时间与结束时间间隔
if @timeend < @noonworkend then
set interval_minute = interval_minute + timestampdiff(minute, concat(@startdate,' ',@timestart), concat(@startdate,' ', @timeend));
-- 结束时间大于下午下班时间，计算 开始时间与下午下班时间间隔
elseif @timeend >= @noonworkend then
set interval_minute = interval_minute + timestampdiff(minute, concat(@startdate,' ',@timestart), concat(@startdate,' ', @noonworkend));
end if;
end if;
-- 不是同一天的情况
if startiswork = 1 then
-- 工作日减去1天
set workdaynum = workdaynum - 1;
-- 小于上午上班时间，计算 一天
if @timestart <= @monworkstart then
set interval_minute = interval_minute + 480;
-- 处于上午工作时间，计算 开始时间与上午下班时间间隔+半天
elseif @timestart <= @monworkend then
set interval_minute = interval_minute + timestampdiff(minute, starttime, concat(@startdate,' ', @monworkend)) + 240;
-- 处于午休区间，计算 半天
elseif @timestart <= @noonworkstart then
set interval_minute = interval_minute + 240;
-- 处于下午工作时间，计算 开始时间与下午下班时间间隔
elseif @timestart <= @noonworkend then
set interval_minute = interval_minute + timestampdiff(minute, starttime, concat(@startdate,' ', @noonworkend));
end if;
end if;
if endiswork = 1 then
-- 工作日减去1天
set workdaynum = workdaynum - 1;
-- 小于上午上班时间，计算 0
if @timeend <= @monworkstart then
set interval_minute = interval_minute + 0;
-- 处于上午工作时间，计算 上午上班时间与结束时间间隔
elseif @timeend <= @monworkend then
set interval_minute = interval_minute + timestampdiff(minute, concat(@enddate,' ', @monworkstart), endtime);
-- 处于午休时间，计算 半天
elseif @timeend <= @noonworkstart then
set interval_minute = interval_minute + 240;
-- 处于下午工作时间，计算 半天+下午上班时间与结束时间间隔
elseif @timeend <= @noonworkend then
set interval_minute = interval_minute +240 + timestampdiff(minute, concat(@enddate,' ', @noonworkstart), endtime);
-- 大于下午下班时间，计算 一天
elseif @timeend > @noonworkend then
set interval_minute = interval_minute + 480;
end if;
end if;
end if;
-- 计算得到最终的工作分钟数
return interval_minute + workdaynum * 480;
我们使用4月的日历进行测试，测试结果如下：
实测5万5千条记录，耗时1分10秒，当然这是在本机docker上的mysql跑的结果，本机的配置比较低，CPU为i5-8250U，mysql最大内存1G
函数在数据量少的情况下还是可以使用，多了就会占用宝贵的数据库资源，得不偿失!
