Блог

У меня есть две таблицы с приведенной ниже структурой.

Таблица задач

Таблица времени выполнения задач

Я хочу получить время между start_date_time и end_date_time в днях, часах и минутах. Время между 9 утра и 5 вечера является рабочим временем и должно учитываться только для расчета. Например, если start_date_time есть 2019-03-25 09:00:00 и end_date_time is 2019-03-26 17:00:00 , то расчетное время составит 2 дня. Если end_date_time равно 2019-03-26 12:00:00 , то расчетное время составит 1 день 3 часа. Одна и та же задача может содержать несколько записей о времени, и общее время должно быть рассчитано. Я пытался с помощью приведенного ниже запроса получить время, но это дает общее количество часов. Мне нужны только часы между 9 утра и 5 вечера. Скрипка с БД

 SELECT t.task_name, sum(time_to_sec(timediff(end_date_time, start_date_time )) / 3600) FROM `task_time` tt left join task t on tt.task_id=t.task_id
group by tt.task_id
  

Самый простой способ справиться с этим — вычислить дни и часы отдельно, а затем просто посчитать день как 8 часов. Что-то вроде этого:

 SELECT t.task_name, 
       SUM(TIMESTAMPDIFF(DAY, DATE(start_date_time), DATE(end_date_time)) * 8  
           TIMESTAMPDIFF(MINUTE, TIME(start_date_time), TIME(end_date_time)) / 60) AS task_time
FROM task t
JOIN task_time tt ON tt.task_id = t.task_id
GROUP BY t.task_name
  

Вывод (для моих демонстрационных данных)

 task_name   task_time
task1       16
task2       11
task3       4
  

Демо на dbfiddle

Обновить

Этот запрос позволит определять время начала и окончания вне пределов 9 утра-5 вечера, считая только часы между 9 утра и 5 вечера в качестве рабочих часов. Это работает, вводя время начала и окончания в диапазон 9 утра-5 вечера с помощью GREATEST и LEAST :

 SELECT t.task_name, 
       SUM(TIMESTAMPDIFF(DAY, start_date, end_date) * 8  
           TIMESTAMPDIFF(MINUTE, TIME(start_time), TIME(end_time)) / 60) AS task_time
FROM task t
JOIN (SELECT task_id,
             DATE(start_date_time) AS start_date,
             DATE(end_date_time) AS end_date,
             GREATEST('09:00', LEAST('17:00', TIME(start_date_time))) AS start_time,
             GREATEST('09:00', LEAST('17:00', TIME(end_date_time))) AS end_time
      FROM task_time) tt ON tt.task_id = t.task_id
GROUP BY t.task_name
  

Обновленная демонстрация

Обновление 2

Этот запрос возвращает время в виде дней, часов и минут, а не просто часов.

 SELECT t.task_name,
       days   FLOOR(minutes / 480) AS days,
       CASE WHEN minutes < 0 THEN 8   FLOOR(minutes % 480 / 60)
            ELSE FLOOR(minutes % 480 / 60)
            END AS hours,
       CASE WHEN minutes < 0 THEN (60   (minutes % 60)) % 60
            ELSE minutes % 60
            END AS minutes
FROM (SELECT t.task_name, 
             SUM(TIMESTAMPDIFF(DAY, start_date, end_date)) AS days,
             SUM(TIMESTAMPDIFF(MINUTE, TIME(start_time), TIME(end_time))) AS minutes
      FROM task t
      JOIN (SELECT task_id,
                   DATE(start_date_time) AS start_date,
                   DATE(end_date_time) AS end_date,
                   GREATEST('09:00', LEAST('17:00', TIME(start_date_time))) AS start_time,
                   GREATEST('09:00', LEAST('17:00', TIME(end_date_time))) AS end_time
            FROM task_time) tt ON tt.task_id = t.task_id
      GROUP BY t.task_name) t
  

Обновленная демонстрация