首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >在不创建函数或视图的情况下计算两个日期之间的营业时间

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

Stack Overflow用户
提问于 2019-06-04 00:14:55
回答 1查看 281关注 0票数 1

我意识到这可能是一个有点多余的问题,但我一直在努力遵循我确实找到的一些例子,我想我应该再问一遍,提供关于我的特定场景的细节。

这就是我为什么要使用:

  • Oracle数据库
  • 日期格式为时间戳格式
  • 我无法创建任何其他表/视图(由于权限问题)
  • 我无法创建任何自定义函数(由于权限问题)
  • 我每周工作40小时,周一至周五的营业时间为8至4:30。(我想从技术上讲,这让我们有40多个小时来解决b/c问题,我不想太花哨地担心排除午餐休息时间)

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

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-06-04 02:40:18

从周五上午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

这是一个包含整个查询的。请注意,在小提琴中,我已经将数据库会话的NLS_TERRITORY设置更改为美国,以使查询工作,因为每周的第一天是特定于国家的。小提琴中的第二个查询替换了特定于地区的函数:

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
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/56431068

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档