mysql - 返回时间大于 22 :00 and smaller than 06:00 for group of different timestamps 的 MySql 查询

Question

我正在开发一个工作时间注册器，我想知道是否有人有一个简单的解决方案来从时间戳数组中选择“夜间时间”，它代表工作的开始和结束(当然这可能是一夜之间)。

示例时间戳:

SELECT start, end FROM work

start                   end
2016-05-23 18:13:59     2016-05-24 02:12:45
2016-05-24 18:12:47     2016-05-25 02:13:39
2016-05-25 17:39:28     2016-05-26 01:37:35
2016-05-26 17:39:42     2016-05-27 01:39:31
2016-05-30 01:59:43     2016-05-30 10:41:37
2016-05-31 01:55:23     2016-05-31 10:49:11
2016-06-01 02:01:21     2016-06-01 12:03:13

我想返回总时间(在一个字段中)，这是在 22:00:00 之后和 06:00:00 之前 - 所以夜间时间。有任何想法吗？谢谢。

Answer 1

这假设您在问题下的评论中提供的信息 maximum set of night hours 始终为 1。希望这会简化一些事情。 下面对其进行了轻微扩展。它允许 worker 在每个类次的 2 个日历日 block 中打多达 3 个夜间时间 block 。这些由 3 个范围表示。如果轮类的总工作时间超过 2 个日历日，则可以轻松扩展它。但是请注意，示例数据的最后一行是 41 小时的长类次。只问是否需要如何扩展它。但相同的数据显示了各种边界条件测试，希望现在涵盖所有内容。

架构

create table times
(   id int auto_increment primary key,
    startDT datetime not null,
    endDT datetime not null
);

示例数据

insert times(startDT,endDT) values
('2016-05-23 18:13:59','2016-05-24 02:12:45'),
('2016-05-24 18:12:47','2016-05-25 02:13:39'),
('2016-05-25 17:39:28','2016-05-26 01:37:35'),
('2016-05-26 17:39:42','2016-05-27 01:39:31'),
('2016-05-30 01:59:43','2016-05-30 10:41:37'),
('2016-05-31 01:55:23','2016-05-31 10:49:11'),
('2016-06-01 02:01:21','2016-06-01 12:03:13'),
('2016-06-01 05:30:00','2016-06-01 13:00:00'),
('2016-06-01 05:30:00','2016-06-01 22:35:00'),
('2016-06-01 05:30:00','2016-06-01 22:30:00'),
('2016-06-01 05:30:00','2016-06-02 22:30:00');

显示调试信息的查询

select id,startDt,endDt,
coalesce(@r1Begin:=concat(date(startDt),' 00:00:00'),null) as `@r1Begin`,
coalesce(@r1End:=concat(date(startDt),' 06:00:00'),null) as `@r1End`,
coalesce(@r2Begin:=concat(date(startDt),' 22:00:00'),null) as `@r2Begin`,
coalesce(@r2End:=concat(date(date_add(date(startDt),interval 1 day)), ' 06:00:00'),null) as `@r2End`,
coalesce(@r3Begin:=concat(date(date_add(date(startDt),interval 1 day)), ' 22:00:00'),null) as `@r3Begin`,
coalesce(@r3End:=concat(date(date_add(date(startDt),interval 1 day)), ' 23:59:59'),null) as `@r3End`,
(secondsOverlapped(startDt,endDt,@r1Begin,@r1End) + secondsOverlapped(startDt,endDt,@r2Begin,@r2End) + secondsOverlapped(startDt,endDt,@r3Begin,@r3End) ) / 3600 as graveyardHours
from times;

上面放大到只有 4 列

+----+---------------------+---------------------+----------------+
| id | startDt             | endDt               | graveyardHours |
+----+---------------------+---------------------+----------------+
|  1 | 2016-05-23 18:13:59 | 2016-05-24 02:12:45 |         4.2125 |
|  2 | 2016-05-24 18:12:47 | 2016-05-25 02:13:39 |         4.2275 |
|  3 | 2016-05-25 17:39:28 | 2016-05-26 01:37:35 |         3.6264 |
|  4 | 2016-05-26 17:39:42 | 2016-05-27 01:39:31 |         3.6586 |
|  5 | 2016-05-30 01:59:43 | 2016-05-30 10:41:37 |         4.0047 |
|  6 | 2016-05-31 01:55:23 | 2016-05-31 10:49:11 |         4.0769 |
|  7 | 2016-06-01 02:01:21 | 2016-06-01 12:03:13 |         3.9775 |
|  8 | 2016-06-01 05:30:00 | 2016-06-01 13:00:00 |         0.5000 |
|  9 | 2016-06-01 05:30:00 | 2016-06-01 22:35:00 |         1.0833 |
| 10 | 2016-06-01 05:30:00 | 2016-06-01 22:30:00 |         1.0000 |
| 11 | 2016-06-01 05:30:00 | 2016-06-02 22:30:00 |         9.0000 |
+----+---------------------+---------------------+----------------+

id 11 是一个 41 小时轮类，涉及 3 个灰场小时段，但只占 9 个灰场小时:1/2 小时 + 8 小时 + 1/2 小时

1 行 1 列的最终查询

select sum(graveyardShiftHoursWorked) as graveYardHours
from
(   select id,startDt,endDt,
    coalesce(@r1Begin:=concat(date(startDt),' 00:00:00'),null) as `@r1Begin`,
    coalesce(@r1End:=concat(date(startDt),' 06:00:00'),null) as `@r1End`,
    coalesce(@r2Begin:=concat(date(startDt),' 22:00:00'),null) as `@r2Begin`,
    coalesce(@r2End:=concat(date(date_add(date(startDt),interval 1 day)), ' 06:00:00'),null) as `@r2End`,
    coalesce(@r3Begin:=concat(date(date_add(date(startDt),interval 1 day)), ' 22:00:00'),null) as `@r3Begin`,
    coalesce(@r3End:=concat(date(date_add(date(startDt),interval 1 day)), ' 23:59:59'),null) as `@r3End`,
    (secondsOverlapped(startDt,endDt,@r1Begin,@r1End) + secondsOverlapped(startDt,endDt,@r2Begin,@r2End) + secondsOverlapped(startDt,endDt,@r3Begin,@r3End) ) / 3600 as graveyardShiftHoursWorked
    from times
) xDerived;

+----------------+
| graveyardHours |
+----------------+
|        39.3674 |
+----------------+

使用的函数

以下是一个用户定义的函数，它采用工作日期时间开始和结束，并确定与提供的范围重叠的秒数以将其与之进行比较。我把它作为练习留给读者去完善它，以便解决所有错误陷阱。例如，如果提供的工作日范围有工作人员的结束 datetime 发生在开始 datetime 之前(即:您的数据有误)，等等。

请注意，该函数返回 seconds。在查询本身中，使用此函数除以 3600 以转换为小时。

drop function if exists secondsOverlapped;
DELIMITER $$
create function secondsOverlapped(r1Begin datetime,r1End datetime,r2Begin datetime,r2End datetime)
RETURNS int DETERMINISTIC
BEGIN
    DECLARE beginOverride datetime;
    DECLARE endOverride datetime;
    DECLARE elapsedSeconds int; 

    IF (r1End<=r2Begin) or (r2End<=r1Begin) THEN
        return 0;
    END IF;
    set beginOverride=greatest(r1Begin,r2Begin);
    set endOverride=least(r1End,r2End);
    set elapsedSeconds=TIME_TO_SEC(TIMEDIFF(endOverride,beginOverride));
    return elapsedSeconds;
END$$
DELIMITER ;

CREATE PROCEDURE and CREATE FUNCTION 的 Mysql 手册页和最小和最大 functions