moomoo
moomoo

Reputation: 11

How to calculate the number of hours that are 'night shift' hours Postgresql

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

Answers (2)

user330315
user330315

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.

Online example

Upvotes: 2

Ajan Balakumaran
Ajan Balakumaran

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

Related Questions