mysql - 获取两个日期之间上午 9 点至下午 5 点之间的时间

Question

我有两个具有以下结构的表。

任务表

任务时间表

我想获取 start_date_time 和 end_date_time 之间的时间(以天、小时和分钟为单位)。上午 9 点至下午 5 点之间的时间为工作时间，仅应考虑计算。例如，如果 start_date_time 为 2019-03-25 09:00:00 并且 end_date_time 为 2019-03-26 17:00 :00 计算出的时间为2天。如果 end_date_time 为 2019-03-26 12:00:00，则计算的时间将为 1 天 3 小时。同一任务可以有多个时间条目，应计算总时间。我尝试使用下面的查询来获取时间，但它给出了总小时数。我只想要上午 9 点到下午 5 点之间的时间。 DB Fiddle

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

Answer 1

处理这个问题最简单的方法是分别计算天数和小时数，然后将一天算作 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

Demo on dbfiddle

更新

此查询允许开始时间和结束时间在上午 9 点到下午 5 点之外，同时仅将上午 9 点到下午 5 点之间的时间计算为工作时间。它的工作原理是使用 GREATEST 和 LEAST 强制开始和结束时间在上午 9 点到下午 5 点范围内:

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

Updated demo

更新 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

Updated demo