Oracle查询调整：将8小时工作制工时计算改为8.5小时

当前查询按8小时工作制（早8点至晚17点）计算工时，需调整为8.5小时工作制（早8点至晚17:30）。给定起止时间20-oct-2022 09:00:00到20-oct-2022 17:30:00，预期返回8.5工时，但当前返回8工时，以下是修改后的查询语句及核心调整点：

修改后的查询语句 #

with dates as (
              select  to_date('20-oct-2022 09:00:00','dd-mon-yyyy hh24:mi:ss') start_dt,
                     to_date('20-oct-2022 17:30:00','dd-mon-yyyy hh24:mi:ss') end_dt
             from  dual
              ),
        -- 获取每日有效工时区间
        t as (
              select  case level
                        when 1 then greatest(start_dt,trunc(start_dt) + 8 / 24) -- 每日上班时间为早8点
                        else trunc(start_dt) + level - 16 / 24 -- 后续日期的上班时间，等价于trunc(start_dt)+(level-1)+8/24
                      end start_dt,
                      case connect_by_isleaf
                        when 1 then least(end_dt,trunc(end_dt) + 17.5 / 24) -- 下班时间改为晚17:30
                        else trunc(start_dt) + level - 6.5 / 24 -- 非最后一天的下班时间，保证为当日17:30
                      end end_dt
                from  dates
                connect by level <= trunc(end_dt) - trunc(start_dt) + 1
             )
select  sum(greatest(end_dt - start_dt,0)) * 24 work_hours
  from  t
  where trunc(start_dt) - trunc(start_dt,'iw') < 5 -- 仅计算工作日（排除周末）

核心调整点 #

• 下班时间基准修改：将原8小时制的下班时间17/24（对应17点）替换为17.5/24（对应17:30），覆盖最后一天的结束时间判断和非最后一天的结束时间计算
• 非最后一天的下班时间公式：原公式trunc(start_dt) + level -7/24调整为trunc(start_dt) + level -6.5/24，确保跨天场景下每日下班时间仍为17:30

内容的提问来源于stack exchange，提问作者Iqra khan