如何计算``夜班''小时数的小时数

``````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
``````

``````shift_id, night_hrs, reg_hrs
1233, 5.5, 1.5
1234, 5, 1
``````

• eos_et 回复

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.

仅存在派生表（子查询），因此在进行减法时，我不必为范围重复表达式。

Online example

• 习惯性隐身 回复

您的逻辑应与此类似，

``````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
``````