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

What would the desired result look like? Also, it's often better to store date and time as a single entity — Strawberry, Jun 01 '14 at 00:12
@PatrickQ yes it is possible (i.e TaskA 04:00 - 06:00, TaskB 07:00 - 08:00) and in this case the total will be 3 Hours because from (04:00 - 06:00) is 2 Hours and (07:00 - 08:00) is 1 hour. — Hadi.M, Jun 01 '14 at 00:18
@Strawberry yes you are right to store in single entity, but it won't makes any difference. The desired result for the Example I gave is 5 Hours. — Hadi.M, Jun 01 '14 at 00:20
This might be something better done in code. So write a query that simply returns the start and end time for each task. Loop through the result array, and for each row, add the difference between `start` and `end` into a `$totalHours` variable. Then, if the difference between the current `end` and the next `start` is negative, subtract that value from `$totalHours`. If it's possible that tasks don't always start right on an hour (for example, starting at `5:25`), then it may be better to convert to seconds before calculating the difference. — Patrick Q, Jun 01 '14 at 00:44

score 1 · Accepted Answer · answered Jun 01 '14 at 01:03

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.

you are genius bro, thanks it worked as expected (:, but my question is there another way to do it without using nested select query ? — Hadi.M, Jun 01 '14 at 08:11
@Hadi.M There is a way, but it's slower, and no less complicated! — Strawberry, Jun 01 '14 at 10:47

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

1 Answers1