mysql 时间段差_MySQL-计算两个日期时间之间的净时间差，同时排除休息时间？

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

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?

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.

解决方案

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

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