mySQL overlapping times

Question

I have a table called shifts which has client_id, employee_id, Shift_start, shift_end.

shift_start and shift_end are timestamps.

employees (nurses) are allocated to the client and are often on 24 hour shifts, so the following is a typical situation.

client_id   employee_id     shift_start            shift_end

15              100         2018-09-01 07:00:00    2018-09-01 19:00:00
15              200         2018-09-01 19:00:00    2018-09-02 07:00:00

Now assume employee 200 is 30 minutes late, her shift_start should be 2018-09-01 19:30:00 and employee 100 shift_end should be changed to 2018-09-01 19:30:00. Straight forward so far.

however imagine employee 200 dishonestly does not change the shift start time and claims she came on time. Then we would have more than 24 hours and cannot bill the client for more than 24 hours a day.

How could I search for overlapping shift times in this scenario?

I have a query that looks for shifts more than 24 hours and it works like a charm. However the above scenario perhaps is two 6 hour shifts but a similar scenario like so:

client_id   employee_id     shift_start            shift_end

15              100         2018-09-01 06:00:00    2018-09-01 12:30:00
15              200         2018-09-01 12:00:00    2018-09-02 18:00:00

My query for the 24 hour scenario is as follows:

select  client_id,  date(shift_start) ,
sum(time_to_sec(timediff(shift_end, shift_start )) / 3600) as totalTime 
from shifts
where shift_month = '2018-09'
group by  date(shift_start),client_id
having sum(time_to_sec(timediff(shift_end, shift_start )) / 3600) > 24

How could I find the overlap regardless of how long the shifts actually are?

You would normally use a join – Strawberry Sep 23 '18 at 09:02 — Strawberry, Sep 23 '18 at 09:02

score 0 · Answer 1 · answered Sep 23 '18 at 09:51

If I understand the issue correctly, then you need the minimum time between the reported shifts and total period of time in which a service was given

so we need to sum the shifts and find the difference between the 1st and last time reports

SELECT 
client_id, 
dte, 
total_reported_time, 
(time_to_sec(timediff(max_shift_end, min_shift_start )) / 3600) as shift_time, 
LEAST(total_reported_time, (time_to_sec(timediff(max_shift_end, min_shift_start )) / 3600)) AS min_shift_time
FROM 

(
SELECT
client_id,  date(shift_start) AS dte,
sum(time_to_sec(timediff(shift_end, shift_start )) / 3600) as total_reported_time, 
MAX(shift_end) AS max_shift_end, 
MIN(shift_start) AS min_shift_start, 
FROM
shifts
WHERE 
shift_month = '2018-09'
GROUP BY
client_id, dte
) a
GROUP BY
client_id, dte

score 0 · Accepted Answer · answered Sep 23 '18 at 09:54

0

Shifts a and b overlap if a.shift_end > b.shift_start and a.shift_start < b.shift_end(see search for interval overlap in list of intervals?)

so

select * from shifts a inner join shifts b on a.client_id = b.client_id 
   and a.shift_end > b.shift_start and a.shift_start < b.shift_end

should give you the overlapping shifts. But this would give you the shifts twice, with a and b switched, Limiting the join to only later beginning shifts should do the trick:

select * from shifts a inner join shifts b on a.client_id = b.client_id 
   and a.shift_end > b.shift_start and a.shift_start < b.shift_end
   and a.shift_start < b.shift_start

answered Sep 23 '18 at 09:54

Turo

4,724
2
14
27

I got it right but your solution is far better than mine. – Wayne Sep 23 '18 at 10:29
Your final comparison seems redundant to me – Strawberry Sep 23 '18 at 12:36
as i wrote, without it you'll get 2 rows with the same information for one overlap, just the position of the values in the result row will be switched: shift a: 1 - 19, shift b: 18 - 24. youll get 2 rows: 1,19,18,24 and 18,24,1,19 – Turo Sep 23 '18 at 14:32

mySQL overlapping times

2 Answers2