Reputation: 11
I'm trying to work out how many hours should be counted as 'night shift' where night shift starts at 23:00 and finishes at 06:00.
We have:
shift_id, start_datetime, end_datetime
1233, '2019-10-01 23:30:00', 2019-10-02 07:30:00
1234, '2019-10-01 22:00:00', 2019-10-02 04:00:00
The desired outcome for the 2 examples above would be
shift_id, night_hrs, reg_hrs
1233, 5.5, 1.5
1234, 5, 1
I'm really struggling to figure this one out so any help would be amazing!
Upvotes: 0
Views: 693
Reputation:
I would use a timestamp range and the intersection with the defined nightshift to calculate this.
select shift_id,
start_datetime, end_datetime,
upper(in_night) - lower(in_night) as time_during_night
from (
select shift_id,
start_datetime, end_datetime,
tsrange(start_datetime, end_datetime) * tsrange(start_datetime::date + time '23:00', end_datetime::date + time '06:00') in_night
from data
) t
tsrange(start_datetime, end_datetime)
creates a timestamp range and tsrange(start_datetime::date + time '23:00', end_datetime::date + time '06:00')
creates the coresponding range for those dates for the night shift.
The *
operator then calculates the intersection between those two ranges. The outer query then simply subtracts the lower end of that range from the upper, yielding an interval that contains the duration during the night shift.
The derived table (sub-query) is only there, so that I don't have to repeat the expression for the ranges when doing the subtraction.
Upvotes: 2
Reputation: 1649
Your logic should be similar to this,
Case when cast(end_datetime as time)>=06:00:00 then (cast(enddate as date) + 06:00:00) else end_datetime end
-
Case when cast(start_datetime as time)<=23:30:00 then (cast(startdate as date)+ 23:30:00) else start_datetime end as Night_hrs
Upvotes: 0