MySQL-计算两个日期时间之间的净时间差，同时排除休息时间? [英] MySQL - Calculate the net time difference between two date-times while excluding breaks?

问题描述

在MySQL查询中，我正在使用 timediff/time_to_sec 函数来计算两个日期时间之间的总分钟数.

In a MySQL query I am using the timediff/time_to_sec functions to calculate the total minutes between two date-times.

例如:

2010-03-23 10:00:00
-
2010-03-23 08:00:00
= 120 minutes

我想做的是排除在选定的时间范围内发生的任何休息.

What I would like to do is exclude any breaks that occur during the selected time range.

例如:

2010-03-23 10:00:00
-
2010-03-23 08:00:00
-
(break 08:55:00 to 09:10:00)
= 105 minutes

是否有一个好的方法可以做到这一点而无需求助于一长串的嵌套IF语句?

Is there a good method to do this without resorting to a long list of nested IF statements?

UPDATE1:

为了澄清-我正在尝试计算用户完成给定任务所需的时间.如果他们喝咖啡休息时间，则该时间段应排除在外.喝咖啡休息时间是固定的时间.

To clarify - I am trying to calculate how long a user takes to accomplish a given task. If they take a coffee break that time period needs to be excluded. The coffee breaks are a at fixed times.

推荐答案

将这段时间内发生的所有中断加总，然后减去timediff/time_to_sec函数的结果

sum all your breaks that occur during the times, and then subtract to the result of the timediff/time_to_sec function

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')

您可以在下一个查询中对其进行计算:

You can calculate it with next query:

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

这篇关于MySQL-计算两个日期时间之间的净时间差，同时排除休息时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！