计算工作时长,排除双休、及午休时间
DROP PROCEDURE IF EXISTS CalculateActualTime;
DELIMITER
CREATE PROCEDURE CalculateActualTime(
IN workStartTime TIME,
IN workEndTime TIME,
IN breakStartTime TIME,
IN breakEndTime TIME,
IN actualStartTime DATETIME,
IN actualEndTime DATETIME,
IN workPattern INT
)
BEGIN
DECLARE workHoursPerDay INT;
DECLARE totalWorkHours INT;
DECLARE totalWorkSeconds INT;
DECLARE totalRestDays INT;
DECLARE currentDay DATETIME;
DECLARE start_time TIME;
DECLARE end_time TIME;
SET workHoursPerDay = ABS(TIMESTAMPDIFF(hour , workStartTime, workEndTime)) -
ABS(TIMESTAMPDIFF(hour , breakStartTime, breakEndTime));
IF actualEndTime IS NULL THEN
SET actualEndTime = NOW();
END IF;
SET currentDay = actualStartTime;
SET totalWorkHours = 0;
SET currentDay = DATE_ADD(currentDay, INTERVAL 1 DAY);
WHILE date_format(currentDay, '%Y-%m-%d') < date_format(actualEndTime, '%Y-%m-%d') DO
IF (workPattern = 0 AND DAYOFWEEK(currentDay) = 1) OR
(workPattern = 1 AND DAYOFWEEK(currentDay) IN (1, 7)) THEN
SET totalRestDays = totalRestDays + 1;
ELSE
SET totalWorkHours = totalWorkHours + workHoursPerDay;
END IF;
SET currentDay = DATE_ADD(currentDay, INTERVAL 1 DAY);
END WHILE;
IF date_format(actualStartTime,'%Y-%m-%d') = date_format(actualEndTime,'%Y-%m-%d') THEN
SET start_time = IF(time(actualStartTime) NOT BETWEEN breakStartTime AND breakEndTime,
IF(time(actualStartTime) < workStartTime, workStartTime, time(actualStartTime)),
breakEndTime);
SET end_time = IF(time(actualEndTime) NOT BETWEEN breakStartTime AND breakEndTime,
IF(time(actualEndTime) > workEndTime, workEndTime, time(actualEndTime)),
breakStartTime);
SET totalWorkSeconds = ABS(TIMESTAMPDIFF(SECOND, start_time, end_time));
ELSE
IF time(actualStartTime) < breakStartTime THEN
SET totalWorkSeconds = ABS(TIMESTAMPDIFF(SECOND, workEndTime,
IF(time(actualStartTime) < workStartTime ,
workStartTime, time(actualStartTime)))) -
ABS(TIMESTAMPDIFF(SECOND, breakStartTime, breakEndTime));
ELSE
SET totalWorkSeconds = ABS(TIMESTAMPDIFF(SECOND, workEndTime,
IF(time(actualStartTime) < breakEndTime ,
breakEndTime, time(actualStartTime))));
END IF;
IF time(actualEndTime) > breakEndTime THEN
SET totalWorkSeconds = totalWorkSeconds
+ ABS(TIMESTAMPDIFF(SECOND, IF( time(actualEndTime) > workEndTime,
workEndTime, time(actualEndTime)),
workStartTime))
- ABS(TIMESTAMPDIFF(SECOND, breakStartTime, breakEndTime));
ELSE
SET totalWorkSeconds = totalWorkSeconds
+ ABS(TIMESTAMPDIFF(SECOND, IF( time(actualEndTime) > breakStartTime,
breakStartTime, time(actualEndTime)),
workStartTime));
END IF;
END IF;
SELECT CONCAT(
IF((totalWorkSeconds + totalWorkHours * 3600) >= 0,
CONCAT(FLOOR((totalWorkSeconds + totalWorkHours * 3600) / 3600 / workHoursPerDay), 'd'),
CONCAT('-', FLOOR(ABS(totalWorkSeconds + totalWorkHours * 3600) / 3600 / workHoursPerDay), 'd')),
FLOOR((totalWorkSeconds + totalWorkHours * 3600) % (workHoursPerDay * 3600) / 3600), 'h',
FLOOR((totalWorkSeconds + totalWorkHours * 3600) % 3600 / 60), 'm',
(totalWorkSeconds + totalWorkHours * 3600) % 60, 's') AS result;
END
DELIMITER ;
call CalculateActualTime('9:00', '18:00', '12:00', '13:00', '2023-11-10 15:00', null, 1);