代码之家 › 专栏 › 技术社区 › superphonic

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

mysql

superphonic · 技术社区 · 7 年前

图式和;数据:

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之外的总工作时间,按天和用户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

正如您所看到的,预期结果仅为每天的加班时间和用户在9到5小时之外的加班时间之和。

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

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

当小提琴决定剥落时使用的粘贴箱: https://pastebin.com/1YvLaKbT

1 回复 | 直到 7 年前

Madhur Bhaiya 7 年前

如果会议的时间跨度是 N 天,您希望在特定会议中按天计算“工作时间”;它敲响了一个钟声,我们可以使用 数字发生器

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

我们将使用数字生成器表来考虑从 start_time 到 end_time . 在这种情况下,我假设会议的时间不可能超过2天。如果它恰好跨越更多的天数,您可以通过添加更多的时间来轻松地扩展范围 UNION ALL SELECT 3 .. 到 ngen Derived Table .

基于此,我们将确定“开始时间”和“结束时间”来考虑正在进行的会议中的特定“工作日期”。此计算是在派生表中为一组 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      |

进一步优化的可能性:

这个查询可以很容易地取消子查询(派生表)的使用。我就是这样写的,以一种可遵循的方式传达数学和过程。但是,您可以轻松地将两者合并 SELECT
,在使用日期/时间函数时可以进行更多优化,并进一步简化其中的数学运算。有关功能详情,请访问: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
一些日期计算会进行多次, , DATE(m.start_time) + INTERVAL ngen.gap DAY User-defined variables ,这也会减少查询的详细程度。
做这个参加 sargable : JOIN .. ON DATE(start_time) + INTERVAL ngen.gap DAY <= DATE(end_time)