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!
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.
ERROR: range lower bound must be less than or equal to range upper bound for example... the following shift where it ends at 23:30 and therefore is 1 hour into night. (1235, '2019-10-01 20:00:00', '2019-10-01 23:30:00')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