如何计算两个日期之间，有多少工作时数（小时），工作小时要去掉节假日和非工作时间

Question

请教各位，如何计算两个日期之间，有多少工作时数（小时），工作小时要去掉节假日和非工作时间，上下班时间分别是：上午8:00到11:30 下午13:30到17:30，比如：2015-11-18 09:00 到2015-11-19 09:00 工作时间为7.5小时，求算法，先谢了。

Answer 1

-- (02) 创建函数：------------
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 > 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')+8/24 -- 如果小于当天8点，将其置为当天8天(因为你是8点上班)
  then
    v_real_startTime:=trunc(v_real_startTime,'dd')+8/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点，将其置为第二天8天
  elsif v_real_startTime>trunc(v_real_startTime,'dd')+18/24
  then
    v_real_startTime:=trunc(v_real_startTime+1,'dd')+8/24;
  end if;  -- 如果小于当天8点，将其置为昨天18点
  if v_real_endTime<trunc(v_real_endTime,'dd')+8/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 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 ),
       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') ), -- 排除周六、日 和 节假日
       c as( select (case when to_char(t1.cdate,'hh24') in ('12','18') then trunc(t1.cdate,'hh') else t1.cdate end) as cdate1, 
                    (case when to_char(t2.cdate,'hh24') in ('14') then trunc(t2.cdate,'hh') else t2.cdate end) as cdate2
             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 ('08','12','14','18') then trunc(t1.cdate,'hh') else t1.cdate end) ),
       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 ('09','10','11','12','15','16','17','18') )
      select sum(d.cdate1-nvl(d.cdate2,d.cdate1))*24 into v_number from d;  return v_number;  
end;
/-- *(03) 测试：
set serveroutput on;
select wk_hours_between('2011-03-21 08:00:00','2011-03-21 12:00:00') from dual;这段代码怎么改下，这个代码只截取了时，我是有分的，哪位大神能解答下，谢谢

Answer 2

SELECT (TO_DATE('2015-11-27 08:00', 'yyyy-mm-dd hh24:mi') -
       TO_DATE('2015-11-26 08:00', 'yyyy-mm-dd hh24:mi'))*24-16.5
  FROM DUAL节假日没有考虑进去，*24-16.5 我觉得工作日始终都是7.5小时

Answer 3

顶一下，请问你是怎么解决的，最近在学Oracle，遇到这种问题