mysql - 如何计算工作时间以外的时间

Question

这最初看起来很简单，但事实证明确实令人头疼。下面是我的表格、数据、预期输出和 SQL Fiddle，说明我在解决问题时所处的位置。

架构和数据:

CREATE TABLE IF NOT EXISTS `meetings` (
  `id` int(6) unsigned NOT NULL,
  `user_id` int(6) NOT NULL,
  `start_time` DATETIME,
  `end_time` DATETIME,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `meetings` (`id`, `user_id`, `start_time`, `end_time`) VALUES
  ('0', '1', '2018-05-09 04:30:00', '2018-05-09 17:30:00'),
  ('1', '1', '2018-05-10 06:30:00', '2018-05-10 17:30:00'),
  ('2', '1', '2018-05-10 12:30:00', '2018-05-10 16:00:00'),
  ('3', '1', '2018-05-11 17:00:00', '2018-05-12 11:00:00'),
  ('4', '2', '2018-05-11 07:00:00', '2018-05-12 11:00:00'),
  ('5', '2', '2018-05-11 04:30:00', '2018-05-11 15:00:00');

我想从上面得到的是 09:00 到 17:00 之外的总工作时间，按天和 user_id 分组。因此，上述数据的结果如下所示:

  date        | user_id | overtime_hours
  ---------------------------------------
  2018-05-09  | 1       | 05:00:00
  2018-05-10  | 1       | 03:00:00
  2018-05-11  | 1       | 07:00:00
  2018-05-12  | 1       | 09:00:00
  2018-05-11  | 2       | 13:30:00
  2018-05-12  | 2       | 09:00:00

如您所见，预期结果只是将每天和用户在朝九晚五以外的时间加类加总。

下面是我所在的查询和SQL Fiddle。主要问题出现在开始和结束跨越午夜(或多个午夜)

SELECT
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIME(end_time)) - TIME_TO_SEC(TIME(start_time)))), user_id, DATE(start_time)
FROM
(SELECT 
    start_time, CASE WHEN TIME(end_time) > '09:00:00' THEN DATE_ADD(DATE(end_time), INTERVAL 9 HOUR) ELSE end_time END AS end_time, user_id
FROM
    meetings
WHERE
    TIME(start_time) < '09:00:00'

UNION

SELECT 
    CASE WHEN TIME(start_time) < '17:00:00' THEN DATE_ADD(DATE(start_time), INTERVAL 17 HOUR) ELSE start_time END AS start_time, end_time, user_id
FROM
    meetings
WHERE
    TIME(end_time) > '17:00:00') AS clamped_times
GROUP BY user_id, DATE(start_time)

http://sqlfiddle.com/#!9/77bc85/1

当 fiddle 决定剥落时的 Pastebin:https://pastebin.com/1YvLaKbT

如您所见，该查询获取了在同一天开始和结束的简单加类时间，但不适用于多天时间。

Answer 1

如果 session 将跨越 n 天，并且您希望计算特定 session 中每天的“工作时间”；它敲响了警钟，我们可以使用数字生成器表。

(SELECT 0 AS gap UNION ALL SELECT 1 UNION ALL SELECT 2) AS ngen

我们将使用数字生成器表来考虑从 start_time 开始的各个日期的单独行。至 end_time .对于这种情况，我假设 session 不太可能超过 2 天。如果碰巧跨越更多天数，您可以通过添加更多 UNION ALL SELECT 3 .. 轻松扩展范围。到 ngen Derived Table .

基于此，我们将确定“开始时间”和“结束时间”以考虑正在进行的 session 中的特定“工作日期”。此计算是在派生表中针对 user_id 的分组进行的和“工作日期”。

之后，我们可以SUM()使用一些数学计算用户每天的“工作时间”。请在下面找到查询。我在其中添加了广泛的评论；如果还有什么不清楚的地方，请告诉我。

---

Demo on DB Fiddle

查询#1

SELECT 
  dt.user_id, 
  dt.wd AS date, 

  SEC_TO_TIME(SUM(

      CASE 
        /*When both start & end times are less than 9am OR more than 5pm*/
        WHEN (st < TIME_TO_SEC('09:00:00') AND et < TIME_TO_SEC('09:00:00')) OR 
             (st > TIME_TO_SEC('17:00:00') AND et > TIME_TO_SEC('17:00:00'))
        THEN et - st  /* straightforward difference between the two times */

        /* atleast one of the times is in 9am-5pm block, OR, 
           start < 9 am and end > 5pm.
           Math of this can be worked out based on signum function */
        ELSE GREATEST(0, TIME_TO_SEC('09:00:00') - st) + 
             GREATEST(0, et - TIME_TO_SEC('17:00:00'))

      END
  )) AS working_hours  

FROM 
(

 SELECT 
   m.user_id, 

   /* Specific work date */
   DATE(m.start_time) + INTERVAL ngen.gap DAY AS wd, 

   /* Start time to consider for this work date */
   /* If the work date is on the same date as the actual start time
      we consider this time */
   CASE WHEN DATE(m.start_time) + INTERVAL ngen.gap DAY = DATE(m.start_time) 
             THEN TIME_TO_SEC(TIME(m.start_time))

        /* We are on the days after the start day */
        ELSE 0  /* 0 seconds (start of the day) */
   END AS st, 

   /* End time to consider for this work date */
   /* If the work date is on the same date as the actual end time
      we consider this time */
   CASE WHEN DATE(m.start_time) + INTERVAL ngen.gap DAY = DATE(m.end_time) 
             THEN TIME_TO_SEC(TIME(m.end_time)) 

        /* More days to come still for this meeting, 
           we consider the end of this day as end time */
        ELSE 86400  /* 24 hours * 3600 seconds (end of the day) */
   END AS et

 FROM meetings AS m 
 JOIN (SELECT 0 AS gap UNION ALL SELECT 1 UNION ALL SELECT 2) AS ngen
   ON DATE(start_time) + INTERVAL ngen.gap DAY <= DATE(end_time)

) AS dt 
GROUP BY dt.user_id, dt.wd;

结果

| user_id | date       | working_hours |
| ------- | ---------- | ------------- |
| 1       | 2018-05-09 | 05:00:00      |
| 1       | 2018-05-10 | 03:00:00      |
| 1       | 2018-05-11 | 07:00:00      |
| 1       | 2018-05-12 | 09:00:00      |
| 2       | 2018-05-11 | 13:30:00      |
| 2       | 2018-05-12 | 09:00:00      |

---

进一步优化的可能性:

1. 这个查询可以很容易地取消子查询(派生表)的使用。我只是这样写的，以一种可遵循的方式传达数学和过程。但是，您可以轻松合并这两个 SELECT block 到单个查询。
2. 也许，可以在日期/时间函数的使用方面进行更多优化，并进一步简化其中的数学。功能详情可在:https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
3. 一些日期计算会进行多次，例如，DATE(m.start_time) + INTERVAL ngen.gap DAY .为避免重新计算，我们可以利用 User-defined variables ，这也会使查询不那么冗长。
4. 使这个JOIN条件sargable : JOIN .. ON DATE(start_time) + INTERVAL ngen.gap DAY <= DATE(end_time)