MYSQL и PHP рассчитывают общее количество часов на конкретную дату и исключают наложения часов

Question

У меня есть таблица MYSQL для задач, где у каждой задачи есть дата, время начала, время окончания и user_id. Я хочу рассчитать общее количество часов на конкретную дату.

Структура таблицы

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"

В результате я получаю часы "06:00", что нормально, но я хочу исключить часы перекрытия. В примере, который я дал, результат должен быть "05:00". Часы, когда час между 3-4 во 2-й записи исключен, потому что этот час уже существует в 1-й записи между 2-4.

• 1-я запись 2=>4 = 2 часа
• 2-я запись 3=>6 = 3 часа 3-1 час =2 (1 час - это час перекрытия между 1-й и 2-й записью)
• 3-й 6=>7=1

Всего 5 часов

Надеюсь, я прояснил свой вопрос. Пример http://sqlfiddle.com/

Answer 1

Вот идея, которая использует переменные (в других базах данных CTE и оконные функции сделали бы это намного проще). Идея состоит в том, чтобы сначала перечислить все время - начинается и заканчивается. Затем следите за суммарным количеством запусков и остановок.

Когда накопленное число больше 0, включите разницу с предыдущим временем. Если он равен 0, то это начало нового периода времени, поэтому ничего не добавляется.

Вот пример запроса, который немного упрощен для ваших данных, так как не отслеживает изменения в 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;

Вот SQL Fiddle, показывающий, как он работает.