MySQL - Вычисляет сетевую разницу во времени между двумя разами в то время как, исключая повреждения?
В запросе MySQL я использую функции timediff/time_to_sec для вычисления общих минут между двумя разами.
Например:
2010-03-23 10:00:00
-
2010-03-23 08:00:00
= 120 minutes
То, что я хотел бы сделать, исключают любые повреждения, которые происходят во время выбранного диапазона времени.
Например:
2010-03-23 10:00:00
-
2010-03-23 08:00:00
-
(break 08:55:00 to 09:10:00)
= 105 minutes
Существует ли хороший метод сделать это, не обращаясь к длинному списку вложенных операторов IF?
UPDATE1:
Для разъяснения - я пытаюсь вычислить, сколько времени пользователь берет для выполнения данной задачи. Если они берут перерыв на кофе, тот период времени должен быть исключен. Перерывы на кофе в фиксированные времена.
1 ответ
просуммируйте все ваши перерывы, которые происходят в течение времени, а затем вычтите результат функции timediff / time_to_sec
SELECT TIME_TO_SEC(TIMEDIFF('17:00:00', '09:00:00')) -- 28800
SELECT TIME_TO_SEC(TIMEDIFF('12:30:00', '12:00:00')) -- 1800
SELECT TIME_TO_SEC(TIMEDIFF('10:30:00', '10:15:00')) -- 900
-- 26100
Предполагая эту структуру:
CREATE TABLE work_unit (
id INT NOT NULL,
initial_time TIME,
final_time TIME
)
CREATE TABLE break (
id INT NOT NULL,
initial_time TIME,
final_time TIME
)
INSERT work_unit VALUES (1, '09:00:00', '17:00:00')
INSERT break VALUES (1, '10:00:00', '10:15:00')
INSERT break VALUES (2, '12:00:00', '12:30:00')
Вы можете вычислить это с помощью следующего запроса:
SELECT *, TIME_TO_SEC(TIMEDIFF(final_time, initial_time)) total_time
, (SELECT SUM(
TIME_TO_SEC(TIMEDIFF(b.final_time, b.initial_time)))
FROM break b
WHERE (b.initial_time BETWEEN work_unit.initial_time AND work_unit.final_time) OR (b.final_time BETWEEN work_unit.initial_time AND work_unit.final_time)
) breaks
FROM work_unit