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

我正在尝试计算应将多少小时计为“夜班”,其中夜班开始于23:00,结束于06:00。

我们有:

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