Oracle 计算两个日期间时间排除非工作日及非工作时间精确至分钟
客户要求计算两个时间点之间工作了多少时间,时间上要排除非工作日、节假日、非工作时间。非工作时间设定为除9点到18点之外的。

在网上搜索一通终于到相类似的情况了,链接为:http://bbs.csdn.net/topics/360105287

网上计算时间点时是自己建了一张节假日的表,好在客户这里另外的系统上有全部的节假日信息,不需要在建表进行维护。用户这边的节假日表是将全年每一天的信息都维护进去,工作日日期标示为1,非工作日日期标示为2.相对简单点。

以下为具体代码:

复制代码
create or replace function wk_hours_between(
i_startTime varchar2, -- 起始时间:( 格式:'YYYY-MM-DD HH24:MI:SS' )
i_endTime varchar2 -- 结束时间:( 格式:'YYYY-MM-DD HH24:MI:SS' )
)
return number
is
v_real_startTime date;--开始时间 变量
v_real_endTime date;--结束时间 变量
v_hours number(18,0);--计算结果
v_number number(18,4);
begin
--格式转换
v_real_startTime := to_date(i_startTime,'YYYY-MM-DD HH24:MI:SS');
v_real_endTime := to_date(i_endTime,'YYYY-MM-DD HH24:MI:SS');
--开始时间及结束时间转换
if v_real_startTime > v_real_endTime -- 如果起始时间大于结束时间,将起始时间与结束时间替换
then
select v_real_startTime, v_real_endTime into v_real_endTime, v_real_startTime from dual;
end if;

if v_real_startTime<trunc(v_real_startTime,'dd')+9/24 -- 如果小于当天9点,将其置为当天9点(因为你是9点上班)
then
v_real_startTime:=trunc(v_real_startTime,'dd')+9/24;
/-- 如果大于当天12点,且小于当天14点,将其置为当天14点(因为你下午是14点上班)
elsif v_real_startTime>trunc(v_real_startTime,'dd')+12/24 and v_real_startTime<trunc(v_real_startTime,'dd')+14/24
then
v_real_startTime:=trunc(v_real_startTime,'dd')+14/24;
/
-- 如果大于当天18点,将其置为第二天9点
elsif v_real_startTime>trunc(v_real_startTime,'dd')+18/24
then
v_real_startTime:=trunc(v_real_startTime+1,'dd')+9/24;
end if;

-- 如果小于当天9点,将其置为昨天18点
if v_real_endTime<trunc(v_real_endTime,'dd')+9/24
then
v_real_endTime:=trunc(v_real_endTime-1,'dd')+18/24;
/ -- 如果大于当天12点,且小于当天14点,将其置为当天12点(因为你上午是12点下班)
elsif v_real_endTime>trunc(v_real_endTime,'dd')+12/24 and v_real_endTime<trunc(v_real_endTime,'dd')+14/24
then
v_real_endTime:=trunc(v_real_endTime,'dd')+12/24;
/
elsif v_real_endTime>trunc(v_real_endTime,'dd')+18/24 -- 如果大于当天18点,将其置为当天18天(因为你是18点下班)
then
v_real_endTime:=trunc(v_real_endTime,'dd')+18/24;
end if;
--with tem as 语句 将后面语句查询结果赋值于tem 使用于多次查询的语句,可简化
with a as( select v_real_startTime+(level-1)/24 as cdate,
trunc(v_real_startTime+(level-1)/24,'hh') as tr_cdate
from dual
connect by level <= (v_real_endTime-v_real_startTime)24+2 ),--connect by level 递归查询 按小时对时间进行拆分
--对a中内容进行筛选,排除非工作日的 to_char 'D' 得到所在所在周内的第几天
b as( select cdate, tr_cdate cdate2 from a where trunc(a.cdate) not in (select hdate from holidays) and to_char(a.cdate,'D') not in ('1','7') ), -- 排除周六、日 和 节假日
--对b中时间点进行转换 主要以工作时间09——18来进行
c as( select (case when to_char(t1.cdate,'hh24') in ('18') then trunc(t1.cdate,'hh') else t1.cdate end) as cdate1, --超过18点的按18点
(case when to_char(t1.cdate2,'hh24') in ('09') then trunc(t2.cdate+1/24,'hh24') else t2.cdate end) as cdate2 --超过9点的按9点
from b t1
left join b t2 on trunc(t1.cdate,'hh24')=trunc(t2.cdate+1/24,'hh24')
order by (case when to_char(t1.cdate,'hh') in ('09','18') then trunc(t1.cdate,'hh') else t1.cdate end) ),
--上一步将超过9点和18点的转换成了整点,此步将整点的字段值信息进行调整
--对c表中信息进行筛选,选取在工作时间点上的来计算
d as ( select (case when c.cdate1>v_real_endTime then v_real_endTime
else c.cdate1 end) as cdate1,
c.cdate2
from c
where to_char(c.cdate1,'hh24') in ('10','11','12','13','14','15','16','17','18') )
--
1440为时间进度计算到分钟
select nvl(sum(d.cdate1-nvl(d.cdate2,d.cdate1))*1440,0) into v_number from d;

return v_number;
end;
复制代码