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

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

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

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

这就是我合作的原因:

  • 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

热门问答

我刚申请的服务器,缺省给我的是linux,可我要Windows,怎么办?

蒋小爱

腾讯云 · 技术支持 (已认证)

推荐
云服务器提供 不同平台重装:仅支持中国大陆地区(不含中国香港)。 例如,Linux 重装为 Windows,Windows 重装为 Linux 。 参考 重装系统: https://cloud.tencent.com/document/product/213/4933 图片.p...... 展开详请

合作伙伴学院里的学习视频测试题和在线培训系统里的测试题能否提供答案?

骑牛看晨曦love&peace~
推荐

http://tencentcloudxuexi.com 合作伙伴可以登录此平台做练习题,有答案的喔

关于Linux DNS服务器设置问题?

mariolu

腾讯 · 后台开发工程师 (已认证)

CDN及云从业者
推荐

CNAME到XX.com,这个XX.COM本身也是需要能解析ip的。CNAME到XX.COM的意义是你能解析到CDN厂商A的域名XX.COM或者CDN厂商B的域名YY.COM。所以需要提供服务的CDN厂商给你他们的域名。这样,DNS查询链路才是完整的。

云服务器不能访问外部网站?

HappyLau谈云计算

腾讯云 · 云计算高级工程师 (已认证)

专注于公有云,私有云解决方案,在kubernetes,openstack,kvm,ceph,linux,shell有丰富的实战经验。
推荐
不能访问外部网站一般是网络和dns的问题,按照如下步骤排查: 1. 确保CVM有外网ip或者NAT转换,使用ping测试下外网的连通性,如果不通请购买弹性公网IP,先申请后购买参考https://cloud.tencent.com/document/product/215/201...... 展开详请

两台腾讯云的服务器在同一个地区,内网网段也相同,都是172.17.0.X,为啥ping内网地址不通?

HappyLau谈云计算

腾讯云 · 云计算高级工程师 (已认证)

专注于公有云,私有云解决方案,在kubernetes,openstack,kvm,ceph,linux,shell有丰富的实战经验。
推荐已采纳

首先看下两台机器是否在同个vpc内,在同个vpc内默认网络可以互通,如果不在同个vpc内需要通过对等连接或者云联网打通,如果在同个vpc下无法互通,请检查下安全组和os的防火墙规则,放行icmp连接。

求问keil5项目无法下载到开发板该怎么解决?

Supowang

腾讯 · 高级工程师 (已认证)

腾讯物联网操作系统TencentOS tiny产品接口人---岁寒知松柏,做最好的自己!
推荐
请检查st-link的驱动和stlink硬件,如果不行,尝试换其他stlink下载器试试,谢谢! 如果还没有解决,请加TencentOS tiny官方QQ群提问,谢谢! qq.png ... 展开详请

所属标签

扫码关注云+社区

领取腾讯云代金券