Mysql datetime count the hours worked between a specific time-CodePudding

I'm looking for a way to count the hours worked between a given time range.

For example to count from the MySQL data below the hours worked between 22:00 and 06:00.

Using date_start 2022-04-01 21:00:00 and date_end 2022-04-02 08:00:00 the user worked 11 hours total and 8 night hours.

Of course the data could also be something like 2022-04-01 05:00:00 and 2022-04-01 16:00:00 which will then need to output 2 night hours or 2022-04-01 18:00:00and 2022-04-02 03:00:00 which outputs 5 night hours.

MySQL table:

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

Current 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;

Output:

date_start              date_end                night_time
2022-04-01 04:00:00     2022-04-01 16:00:00     02:00
2022-04-02 05:00:00     2022-04-02 23:30:00     -16:00
2022-04-03 06:00:00     2022-04-03 18:00:00     00:00
2022-04-04 12:00:00     2022-04-05 00:00:00     02:00
2022-04-05 19:00:00     2022-04-06 07:00:00     08:00

Currently I still have a problem in my current MySQL when the start_date and end_date both start on the same day and both have night hours. for example 2022-04-02 05:00:00 and 2022-04-02 23:30:00 which has 01:00 night hour in start_date and 1:30 hour in end_date (total night: 02:30 hours)

I am not sure if my current MySQL is the best/fastest way to achieve my goal.

CodePudding user response：

Calculating Time Overlaps

You can calculate the amount of time two date ranges overlap using:

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

For example if the date ranges are:

Date_Start	Date_End	Night_Shift_Start	Night_Shift_End
2022-04-01 21:00:00	2022-04-02 08:00:00	2022-04-01 22:00:00 **	2022-04-02 06:00:00 **

The result would be 8 hours:

Min( EndDate ) - Max( StartDate )	.... As Unix Timestamps	Time Overlap
2022-04-02 06:00:00 (minus) 2022-04-01 22:00:00	1648875600 - 1648846800 = 28800 seconds	08:00:00 hours

Checking for Multiple Overlaps

Since technically a single shift could have both started and ended during "night hours" (22:00 to 06:00) you need check for overlaps on both sides.

Date_Start	Date_End	Night Hours	...
2022-04-05 05:00:00	2022-04-05 23:30:00	2.5 hours	(1 hour) : 2022-04-05 05:00 to 2022-04-05 06:00 (1.5 hours) : 2022-04-05 22:00 to 2022-04-05 23:30

One approach is using the base start/end times to calculate the previous and upcoming "night hour" periods:

SELECT  *
        , TIMESTAMP(DATE(date_start) - INTERVAL 1 DAY, '22:00:00') AS current_start
        , TIMESTAMP(DATE(date_start), '06:00:00') AS current_end
        , TIMESTAMP(DATE(date_start), '22:00:00') AS next_start
        , TIMESTAMP(DATE(date_start)   INTERVAL 1 DAY, '06:00:00') AS next_end
FROM   tasks

Results:

id	date_start	date_end	current_start	current_end	next_start	next_end
1	2022-04-01 04:00:00	2022-04-01 16:00:00	2022-03-31 22:00:00	2022-04-01 06:00:00	2022-04-01 22:00:00	2022-04-02 06:00:00
2	2022-04-02 05:00:00	2022-04-02 23:30:00	2022-04-01 22:00:00	2022-04-02 06:00:00	2022-04-02 22:00:00	2022-04-03 06:00:00
3	2022-04-03 06:00:00	2022-04-03 18:00:00	2022-04-02 22:00:00	2022-04-03 06:00:00	2022-04-03 22:00:00	2022-04-04 06:00:00
4	2022-04-04 12:00:00	2022-04-05 00:00:00	2022-04-03 22:00:00	2022-04-04 06:00:00	2022-04-04 22:00:00	2022-04-05 06:00:00
5	2022-04-05 19:00:00	2022-04-06 07:00:00	2022-04-04 22:00:00	2022-04-05 06:00:00	2022-04-05 22:00:00	2022-04-06 06:00:00
6	2022-04-01 04:00:00	2022-04-01 16:00:00	2022-03-31 22:00:00	2022-04-01 06:00:00	2022-04-01 22:00:00	2022-04-02 06:00:00
7	2022-04-05 19:00:00	2022-04-06 07:00:00	2022-04-04 22:00:00	2022-04-05 06:00:00	2022-04-05 22:00:00	2022-04-06 06:00:00
8	2022-04-05 05:00:00	2022-04-05 23:30:00	2022-04-04 22:00:00	2022-04-05 06:00:00	2022-04-05 22:00:00	2022-04-06 06:00:00

Total Overlap Time

Once you have the "night hour" ranges, calculate the overlapping time on both sides and add them together to get the total time worked during "night hours"

SELECT id
       , date_start
       , date_end
       , SEC_TO_TIME( 
            GREATEST(0, start_overlap__in_seconds)  -- ignore negative time, which means no overlap
              GREATEST(0, end_overlap_in_seconds)
       ) AS time_overall
FROM  ( 
          SELECT * 
                 , UNIX_TIMESTAMP(LEAST(date_end, current_end))
                   - UNIX_TIMESTAMP(GREATEST(date_start, current_start))
                 AS start_overlap__in_seconds                   
                 , UNIX_TIMESTAMP(LEAST(date_end,next_end))
                   - UNIX_TIMESTAMP(GREATEST(date_start,next_start))
                 AS end_overlap_in_seconds                   
          FROM  (
                  SELECT  *
                          , TIMESTAMP(DATE(date_start) - INTERVAL 1 DAY, '22:00:00') AS current_start
                          , TIMESTAMP(DATE(date_start), '06:00:00') AS current_end
                          , TIMESTAMP(DATE(date_start), '22:00:00') AS next_start
                          , TIMESTAMP(DATE(date_start)   INTERVAL 1 DAY, '06:00:00') AS next_end
                  FROM   tasks
                ) tmp 
     ) t

Final Results:

id	date_start	date_end	time_overall
1	2022-04-01 04:00:00	2022-04-01 16:00:00	02:00:00
2	2022-04-02 05:00:00	2022-04-02 23:30:00	02:30:00
3	2022-04-03 06:00:00	2022-04-03 18:00:00	00:00:00
4	2022-04-04 12:00:00	2022-04-05 00:00:00	02:00:00
5	2022-04-05 19:00:00	2022-04-06 07:00:00	08:00:00
6	2022-04-01 04:00:00	2022-04-01 16:00:00	02:00:00
7	2022-04-05 19:00:00	2022-04-06 07:00:00	08:00:00
8	2022-04-05 05:00:00	2022-04-05 23:30:00	02:30:00

db<>fiddle here