排除非工作时间、非工作日后，计算工作时间，代码如下：
-- 删除函数 DROP FUNCTION IF EXISTS calculateWorkingTime; set @workStartTime='09:30:00'; set @workEndTime='18:30:00'; -- 创建函数 --/ CREATE FUNCTION calculateWorkingTime(startDate datetime,endDate datetime) RETURNS decimal(32,4) BEGIN DECLARE intnum int(255) DEFAULT 0; DECLARE decimalnum decimal(32,4) DEFAULT 0.000; DECLARE workStartTimeHour decimal(32,4) DEFAULT 0.000; DECLARE workEndTimeHour decimal(32,4) DEFAULT 0.000; DECLARE currentDay int(10) DEFAULT 0; DECLARE tempTimeHour decimal(32,4) DEFAULT 0.000; -- DECLARE temp varchar(2048) DEFAULT ''; -- deal starttime and endtime is nonworkdays SET startDate = (CASE WHEN weekday(startDate)=5 THEN concat(date(timestampadd(day,2,startDate)),' ',@workStartTime) WHEN weekday(startDate)=6 THEN concat(date(timestampadd(day,1,startDate)),' ',@workStartTime) ELSE startDate END); SET endDate = (CASE WHEN weekday(endDate)=5 THEN concat(date(timestampadd(day,-1,endDate)),' ',@workEndTime) WHEN weekday(endDate)=6 THEN concat(date(timestampadd(day,-2,endDate)),' ',@workEndTime) ELSE endDate END); -- SET temp = concat(temp,' ',startDate,';',endDate); if startDate < endDate then -- deal starttime and endtime is nonworktime if time(startDate)<=@workStartTime THEN SET startDate = concat(date(startDate),' ', @workStartTime); elseif date(startDate) < date(endDate) and time(startDate)>@workEndTime then SET startDate = concat(date(date_add(startDate, interval 1 day)),' ',@workStartTime); end if; if time(endDate)>=@workEndTime then SET endDate = concat(date(endDate),' ',@workEndTime); elseif date(startDate) < date(endDate) and time(endDate)<@workStartTime then SET endDate = concat(date(date_add(endDate, interval -1 day)),' ',@workEndTime); end if; -- calculate time diff SET decimalnum = (minute(endDate)*60+second(endDate)-minute(startDate)*60-second(startDate))/3600; end if; -- calculate work time second SET workStartTimeHour = hour(@workStartTime)+minute(@workStartTime)/60+second(@workStartTime)/3600; SET workEndTimeHour = hour(@workEndTime)+minute(@workEndTime)/60+second(@workEndTime)/3600; -- WHILE (floor((unix_timestamp(endDate) - unix_timestamp(startDate))/3600) > 0) DO WHILE ((floor(unix_timestamp(endDate)/3600) - floor(unix_timestamp(startDate)/3600)) > 0) DO SET tempTimeHour = hour(startDate)+minute(startDate)/60+second(startDate)/3600; if workStartTimeHour <= tempTimeHour and tempTimeHour < workEndTimeHour then -- SET temp = concat(temp,' ',tempTimeHour,';'); SET intnum = (CASE WHEN weekday(startDate)=5 or weekday(startDate)=6 then intnum ELSE intnum+1 END); end if; SET startDate = timestampadd(hour,1,startDate); END WHILE; SET decimalnum = intnum + decimalnum; -- concat(decimalnum,';',workStartTimeHour,' ',workEndTimeHour,' ',intnum,';;;',temp); RETURN decimalnum; END / -- select calculateWorkingTime('2017-02-17 07:30:00','2017-02-21 17:39:00'); select transport_id,create_at1,create_at2,create_at3, calculateWorkingTime(create_at1,create_at2), calculateWorkingTime(create_at2,create_at3),calculateWorkingTime(create_at1,create_at3) from newTable; select transport_id,calculateWorkingTime(create_at1,create_at2) from newTable; |
说明：第一次实现这种需求，当初写的时候又比较赶，写完后，发现，虽然功能实现了，但还有好多地方可以调优
女票是搞数据运营的，经常需要统计员工的工作时间；听女票说，这段SQL代码统计时好像有些问题，但还没来的急分析原因呢；女票又有新需求来了，不仅需要将非工作时间、非工作日去掉，还需要将节假日去掉 我将上面代码重构，实现想要功能（重构后的代码就不放出了）
