MYSQL & PHP calculate total hours in specific date and exclude overlaps hours

Question

I have a MYSQL table for tasks where each task has a date, start time,end time and user_id. I want to calculate total number of hours on specific date.

Table Structure

CREATE TABLE tasks
    (`id` int,`user_id` int, `title` varchar(30), `task_date` datetime, `start` time, `end` time)
;

INSERT INTO tasks
    (`id`,`user_id`, `title`,`task_date`, `start`, `end`)
VALUES
    (1,10, 'Task one','2013-04-02', '02:00:00', '04:00:00'),
    (2,10, 'Task two','2013-04-02', '03:00:00', '06:00:00'),
    (3,10, 'Task three.','2013-04-02','06:00:00', '07:00:00');

MYSQL Query

select  TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF( end, start)))), "%h:%i") AS diff
FROM tasks
where task_date="2013-04-02"

The result am getting is "06:00" Hours which is fine, but I want to exclude the overlap hours. In the example I gave result should be "05:00" Hours when the hour between 3-4 in the 2nd record is excluded because this hour is already exist in the 1st record between 2-4.

• 1st record 2=>4 = 2 Hours
• 2nd record 3=>6 = 3 hours 3-1 hour=2 (The 1 hour is the overlap hour between 1st and 2nd record )
• 3rd 6=>7=1

Total is 5 Hours

I hope I made my question clear. Example http://sqlfiddle.com/#!2/05dd8/2

Answer 1

Here is an idea that uses variables (in other databases, CTEs and window functions would make this much easier). The idea is to first list all the times -- starts and ends. Then, keep track of the cumulative number of starts and stops.

When the cumulative number is greater than 0, then include the difference from the previous time. If equal to 0, then it is the beginning of a new time period, so nothing is added.

Here is an example of the query, which is simplified a bit for your data by not keeping track of changes in user_id:

select user_id, TIME_FORMAT(SEC_TO_TIME(sum(secs)), '%h:%i')
from (select t.*, 
             @time := if(@sum = 0, 0, TIME_TO_SEC(TIMEDIFF(start, @prevtime))) as secs,
             @prevtime := start,
             @sum := @sum + isstart
      from ((select user_id, start, 1 as isstart
             from tasks t
            ) union all
            (select user_id, end, -1
             from tasks t
            )
           ) t cross join
           (select @sum := 0, @time := 0, @prevtime := 0) vars
      order by 1, 2
     ) t
group by user_id;

Here is a SQL Fiddle showing it working.