MySQL - 计算两个日期时间之间的净时差，同时排除休息时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2502584/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - Calculate the net time difference between two date-times while excluding breaks?
提问by John M
In a MySQL query I am using the timediff/time_to_secfunctions to calculate the total minutes between two date-times.
在 MySQL 查询中，我使用timediff/time_to_sec函数来计算两个日期时间之间的总分钟数。
For example:
例如：
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.
我想做的是排除在选定时间范围内发生的任何中断。
For example:
例如：
2010-03-23 10:00:00
-
2010-03-23 08:00:00
-
(break 08:55:00 to 09:10:00)
= 105 minutes
Is there a good method to do this without resorting to a long list of nested IF statements?
有没有一个很好的方法来做到这一点，而无需求助于一长串嵌套的 IF 语句？
UPDATE1:
更新1：
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.
澄清 - 我正在尝试计算用户完成给定任务所需的时间。如果他们休息一下，则需要排除该时间段。咖啡休息时间是固定的。
回答by Jhonny D. Cano -Leftware-
sum all your breaks that occur during the times, and then subtract to the result of the timediff/time_to_sec function
将这段时间内发生的所有休息时间相加，然后减去 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
Assuming this structure :
假设这种结构：
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
