在不创建函数或视图的情况下计算两个日期之间的营业时间

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (11)

我意识到这可能是一个有点多余的问题,但我一直在努力遵循我找到的一些例子,我想我会再次询问我的具体情况。

这就是我合作的原因:

  • Oracle数据库
  • 日期采用时间戳格式
  • 我无法创建任何其他表/视图(由于权限问题)
  • 我无法创建任何自定义功能(由于权限问题)
  • 我周一到周五工作时间为40小时,营业时间为8到4:30。(我觉得从技术上来说,我们花了40多个小时来计算b / c我不想太过担心排除午休时间)

我能够计算出小时计算但我不知道如何进入工作日组件。

提问于
用户回答回答于

从周五上午8点到周一上午9点​​的例子开始:

with dates as (
 Select timestamp '2019-05-31 08:00:00' start_date
      , timestamp '2019-06-03 09:00:00' end_date
   from dual
)

我们需要产生两者之间的日子。我们可以使用递归查询来做到这一点:

, recur(start_date, calc_date, end_date) as (
  -- Anchor Part
  select start_date
       , trunc(start_date)
       , end_date
    from dates

  -- Recrusive Part
  union all
  select start_date
       , calc_date+1
       , end_date
    from recur
   where calc_date+1 < end_Date
)

由此我们需要弄清楚一些事情,例如,calc_day是工作日还是周末,以及calc_day的开始和结束时间是什么,然后我们可以获取这些值并使用一些日期算法来查找数量那天工作的小时数(从我们开始时间戳开始的第二个时间间隔返回):

, days as (
  select calc_date
       , case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end isWeekDay
       , greatest(start_date, calc_date + interval '8' hour) start_time
       , least(end_date, calc_date      + interval '16:30' hour to minute) end_time

       , least( ( least(end_date, calc_date      + interval '16:30' hour to minute) 
                - greatest(start_date, calc_date + interval '8' hour)
                ) * case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end
              , interval '8' hour
         ) daily_hrs
    from recur
   where start_date < (calc_date + interval '16:30' hour to minute)
     and (calc_date + interval '8' hour) < end_date
)

请注意,在上述步骤中,我们将每日工作时间限制为每天8小时,where子句可以防止工作时间以外的开始或结束日期。最后一步是总结小时数。遗憾的是,Oracle没有任何本地间隔聚合或分析函数,但我们仍然可以通过将间隔转换为秒,将它们相加然后将它们转换回输出间隔来进行管理:

select calc_date
     , daily_hrs
     , numtodsinterval(sum( extract(hour   from daily_hrs)*60*60
                          + extract(minute from daily_hrs)*60
                          + extract(second from daily_hrs)
                          ) over (order by calc_date)
                      ,'second') run_sum
  from days;

我已经完成了上面的总和作为分析函数,所以我们可以看到一些介入数据,但如果你只想要最终输出,你可以将查询的最后部分更改为:

select numtodsinterval(sum( extract(hour from daily_hrs)*60*60
                          + extract(minute from daily_hrs)*60
                          + extract(second from daily_hrs)
                          )
                      ,'second') run_sum

这是一个db <>在整个查询中运用的小提琴。请注意,在小提琴中,我已将DB会话的NLS_TERRITORY设置更改为AMERICA,以使查询起作用,因为一周的第一天是特定于国家/地区的。小提琴中的第二个查询替换了区域特定功能:

case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end

与位置和语言无关的计算:

case when (mod(mod(calc_date - next_day(date '2019-1-1',to_char(date '2019-01-06','day')),7),6)) != 0 then 1 end

扫码关注云+社区

领取腾讯云代金券