Mysql 日期时间计算特定时间之间的工作时间

Question

我正在寻找一种计算给定时间范围之间工作时间的方法。

例如，从下面的MySQL数据中计算22：00到06：00之间的工作时间。

使用date_start2022-04-01 21:00:00和date_end2022-04-02 08:00:00，用户11 hours total和8 night hours工作。

当然，数据也可以是类似于2022-04-01 05:00:00和2022-04-01 16:00:00的东西，然后需要输出2 night hours或2022-04-01 18:00:00，2022-04-02 03:00:00输出5 night hours。

MySQL 表：

CREATE TABLE `tasks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_start` datetime DEFAULT NULL,
`date_end` datetime DEFAULT NULL,
UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tasks` (`date_start`,`date_end`) VALUES 
('2022-04-01 04:00:00', '2022-04-01 16:00:00'), # 2:00 nighthours
('2022-04-02 05:00:00', '2022-04-02 23:30:00'), # 2:30 nighthours
('2022-04-03 06:00:00', '2022-04-03 18:00:00'), # 0:00 nighthours
('2022-04-04 12:00:00', '2022-04-05 00:00:00'), # 2:00 nighthours
('2022-04-05 19:00:00', '2022-04-06 07:00:00'); # 8:00 nighthours

当前 MySQL：

# 21600 = 06:00 hours
# 79200 = 22:00 hours
SELECT t.date_start, t.date_end, DATE_FORMAT(TIMEDIFF(
(CASE WHEN DATE(t.date_start) != DATE(t.date_end) AND TIME_TO_SEC(t.date_end) > 21600 THEN DATE_FORMAT(t.date_end, '%Y-%m-%d 06:%i:%s')
WHEN TIME_TO_SEC(t.date_start) < 21600 THEN DATE_FORMAT(t.date_start, '%Y-%m-%d 06:%i:%s')
ELSE t.date_end END), 
(CASE WHEN DATE(t.date_start) != DATE(t.date_end) AND TIME_TO_SEC(t.date_start) < 79200 THEN DATE_FORMAT(t.date_start, '%Y-%m-%d 22:%i:%s')
WHEN TIME_TO_SEC(t.date_end) > 79200 THEN DATE_FORMAT(t.date_start, '%Y-%m-%d 22:%i:%s')
WHEN DATE(t.date_start) = DATE(t.date_end) AND TIME_TO_SEC(t.date_end) <= 79200 AND TIME_TO_SEC(t.date_start) >= 21600 THEN t.date_end
ELSE t.date_start END)
), '%H:%i') AS night_time FROM tasks t;

目前，当start_date和end_date都在同一天启动并且都有夜间时间时，我当前的MySQL仍然存在问题。 例如2022-04-02 05:00:00和2022-04-02 23:30:00，start_date的夜间时间为 01：00，end_date的夜间时间为 1：30(总夜晚：02：30 小时)

我不确定我目前的MySQL是否是实现目标的最佳/最快方法。

Answer 1

计算时间重叠

您可以使用以下方法计算两个日期范围重叠的时间量：

• MIN( EndDate1, EndDate2 ) - MAX( StartDate1, StartDate2 )
  
  

例如，如果日期范围为：

th style="text-align： left;">Date_Endstyle="文本对齐：左;">2022-04-01 21：00：00

Date_Start<<thead>
Night_Shift_Start	Night_Shift_End
2022-04-02 08：00：00	2022-04-01 22：00：00 **	2022-04-02 06：00：00 **