Oracle 计算工时除去节假日 (精确到每天工作时间)
--可精确到每天的工作时间、全年法定休息日、节假日。结果返回小时数 --前提条件：DIM_oa_TIME 包含每一天，并且is_work=1 工作日 =0 非工作日 --详见：https://www.cnblogs.com/xiaobaidejiucuoben/p/14630923.html -- 首先创建工作时间视图 create or replace view VM_WORKTIME as select 1 as id,' 00:00:00' as begintime,' '||AM_WORK_BEGINTIME||':00' as endtime,0 as workhour from WORKTIME_CURRENCY where org_account_id='67086964711' and week_day_name=1 and year in (select max(year) year from WORKTIME_CURRENCY where org_account_id='67086964711') union all select 2 as id,' '||AM_WORK_BEGINTIME||':00' as begintime,' '||AM_WORK_ENDTIME||':00' as endtime,3 as workhour from WORKTIME_CURRENCY where org_account_id='67086964711' and week_day_name=1 and year in (select max(year) year from WORKTIME_CURRENCY where org_account_id='67086964711') union all select 3 as id,' '||AM_WORK_ENDTIME||':00' as begintime,' '||PM_WORK_BEGINTIME||':00' as endtime,0 as workhour from WORKTIME_CURRENCY where org_account_id='67086964711' and week_day_name=1 and year in (select max(year) year from WORKTIME_CURRENCY where org_account_id='67086964711') union all select 4 as id,' '||PM_WORK_BEGINTIME||':00' as begintime,' '||PM_WORK_ENDTIME||':00' as endtime,4 as workhour from WORKTIME_CURRENCY where org_account_id='67086964711' and week_day_name=1 and year in (select max(year) year from WORKTIME_CURRENCY where org_account_id='67086964711') union all select 5 as id, ' '||PM_WORK_ENDTIME||':00' as begintime,' 23:59:59' as endtime,0 as workhour from WORKTIME_CURRENCY where org_account_id='67086964711' and week_day_name=1 and year in (select max(year) year from WORKTIME_CURRENCY where org_account_id='67086964711')
结果为：
-- 下面函数计算同一天内，两时间的工作时长
CREATE OR REPLACE function getonedayworkhour(begintime in date,endtime in date) return number as -- 一天内（工作日）从开始时间到结束时间的工作时长(返回值为多少天) v_begintime date; v_endtime date; v_nextdate date;--下一天0时 v_date date;--当天0时 beforeworkdaynum number(10,5);--开始时间与本区间（开始时间所在）结束时间相差时长 endworkdaynum number(10,5);--结束时间所在区间的开始时间到结束时间相差时长 workhournum number(10,5);--两时间相差几个时长 workdaynum number(10,5);--两时间相差时长(天) id_order_bef int; --记录开始时间VM_WORKTIME 区间号 id_order_end int; --记录结束时间VM_WORKTIME 区间号 begin v_begintime :=begintime; v_endtime :=endtime; v_nextdate :=To_date(To_char(v_begintime, 'yyyy/mm/dd')||' 23:59:59', 'yyyy/mm/dd hh24:mi:ss');-- 开始日期的23:59:59时 --To_date(To_char(Trunc(v_begintime+1), 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss') v_date :=To_date(To_char(Trunc(v_endtime), 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss');-- 结束日期的当天0时 beforeworkdaynum :=0; endworkdaynum :=0; workhournum :=0; if v_begintime is null or v_endtime is null or v_begintime>v_endtime --开始时间大于结束时间 then workhournum :=0; else --计算开始时间到本区间结束时间为多少时间（按天计算） select id into id_order_bef from VM_WORKTIME where To_date(To_char(v_begintime, 'yyyy/mm/dd')|| begintime, 'yyyy/mm/dd hh24:mi:ss')<=v_begintime and To_date(To_char(v_begintime, 'yyyy/mm/dd')|| endtime, 'yyyy/mm/dd hh24:mi:ss')>v_begintime; --开始日期在哪个时间区间 if id_order_bef<>2 and id_order_bef<>4 then beforeworkdaynum :=0; --select endtime into v_begintime from VM_WORKTIME where id=id_order_bef;--把本id_order_bef的endtime 作为v_begintime临时开始时间 else select to_number(To_date(To_char(v_begintime, 'yyyy/mm/dd')|| endtime, 'yyyy/mm/dd hh24:mi:ss')- v_begintime) --- v_begintime参数 into beforeworkdaynum from VM_WORKTIME where id=id_order_bef;--用本id_order_bef的endtime减v_begintime 计算本区间时间 end if; --计算本区间开始时间到结束时间为多少时间（按天计算） select id into id_order_end from VM_WORKTIME where To_date(To_char(v_endtime, 'yyyy/mm/dd')|| begintime, 'yyyy/mm/dd hh24:mi:ss')<v_endtime and To_date(To_char(v_endtime, 'yyyy/mm/dd')|| endtime, 'yyyy/mm/dd hh24:mi:ss')>=v_endtime; --结束日期在哪个时间区间 if id_order_end<>2 and id_order_end<>4 then endworkdaynum :=0; --select endtime into v_endtime from VM_WORKTIME where id=id_order_end;--把本id_order_end的endtime 作为v_endtime临时开始时间 else select to_number( v_endtime-To_date(To_char(v_endtime, 'yyyy/mm/dd')|| begintime, 'yyyy/mm/dd hh24:mi:ss')) --- v_endtime参数 into endworkdaynum from VM_WORKTIME where id=id_order_end; -- ;--用本id_order_end的endtime减v_begintime 计算本区间时间 end if; -- 计算开始时间到结束时间 中间有多少时间（按天计算） if id_order_bef=id_order_end then select round(to_number( v_endtime-v_begintime),5) --- 在同一个区间 结束时间减开始时间 into workdaynum from dual; else select sum(nvl(workhour,0))/24 into workdaynum from VM_WORKTIME where id >id_order_bef and id<id_order_end; --总共时间 workdaynum :=nvl(workdaynum,0)+nvl(beforeworkdaynum,0)+nvl(endworkdaynum,0); end if; end if; return workdaynum; end; ---select getonedayworkhour(to_date('2022-05-27 09:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2022-05-27 23:30:00','yyyy-mm-dd hh24:mi:ss')) from dual; --select To_date(To_char('2022-05-27 23:30:00', 'yyyy/mm/dd')||' 23:59:59', 'yyyy/mm/dd hh24:mi:ss') from dual; --select getonedayworkhour(to_date('2022-06-02 16:34:21','yyyy-mm-dd hh24:mi:ss'),to_date(' 2022-06-02 16:58:58','yyyy-mm-dd hh24:mi:ss')) from dual;
-- 下面函数计算，两时间的工作时长 CREATE OR REPLACE function getworkhour(begindate in date,enddate in date) return number as -- 两时间间隔多少小时（返回结果为小时） v_begindate date; v_enddate date; v_nextdate date;--下一天0时 v_date date;--当天0时 beforeworkhournum number(10,5);--开始时间育当天24点两时间相差几个小时 endworkhournum number(10,5);--结束时间与当天0点两时间相差几个小时 workhournum number(10,5);--两时间相差几个小时 workdaynum number(10,5);--两时间相差几个整天工作日 daynum number(10,5);--两时间相差几个整天 is_work int;--是否为非工作日 begin v_begindate :=begindate; v_enddate :=enddate; v_nextdate :=To_date(To_char(v_begindate, 'yyyy/mm/dd')||' 23:59:59', 'yyyy/mm/dd hh24:mi:ss');-- 开始日期的下一天0时 v_date :=To_date(To_char(Trunc(v_enddate), 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss');-- 结束日期的当天0时 beforeworkhournum :=0; endworkhournum :=0; workhournum :=0; workdaynum :=0; daynum :=0; if v_begindate is null or v_enddate is null or v_begindate>v_enddate --开始日期大于结束日期时 then workhournum :=0; else if to_number(To_date(To_char(v_begindate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss')-To_date(To_char(v_enddate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss'))=0 --开始日期和结束日期时为同一天 then select count(*) into is_work from DIM_oa_TIME where fdate=To_date(To_char(v_begindate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss') and is_work =1; --这天是否节假日 if is_work=0 --or v_begindate is null or v_enddate is null then workhournum :=0; else workhournum := round(getonedayworkhour(v_begindate,v_enddate)*24,5) ; end if; else select count(*) into is_work from DIM_oa_TIME where fdate=To_date(To_char(v_begindate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss') and is_work =1; --开始日期是否节假日 if is_work=0 --or v_begindate is null then beforeworkhournum :=0; else beforeworkhournum :=round(getonedayworkhour(v_begindate,v_nextdate)*24,5)+beforeworkhournum;-- 开始时间到今天天最后一秒的小时数 -- select round(getonedayworkhour(to_date('2022-05-26 08:30:00','yyyy-mm-dd hh24:mi:ss'),To_date(To_char(to_date('2022-05-26 08:30:00','yyyy-mm-dd hh24:mi:ss'), 'yyyy/mm/dd')||' 23:59:59', 'yyyy/mm/dd hh24:mi:ss'))*24,5) from dual; end if; select count(*) into workdaynum from DIM_oa_TIME where fdate>=v_begindate and fdate<=v_enddate and is_work=1; --工作日相差天数 select count(*) into is_work from DIM_oa_TIME where fdate=To_date(To_char(v_enddate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss') and is_work =1; --结束日期是否节假日 if is_work=0 --or v_begindate is null then --如果v_enddate为节假日workdaynum不减1 endworkhournum :=0; else endworkhournum :=round(getonedayworkhour(v_date,v_enddate)*24,5)+endworkhournum;-- 下一天0时到结束时间的小时数 workdaynum :=workdaynum-1;--如果v_enddate不为节假日workdaynum减1 end if; -- daynum :=to_date(To_char(v_enddate, 'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(To_char(v_begindate, 'yyyy/mm/dd'),'yyyy/mm/dd'); --工作日相差天数 end if; end if; workhournum :=nvl(workdaynum,0)*24+nvl(endworkhournum,0)+nvl(beforeworkhournum,0)+nvl(workhournum,0); return workhournum; end; ---select getworkhour(to_date('2022-06-02 16:34:21','yyyy-mm-dd hh24:mi:ss'),to_date(' 2022-06-02 16:58:58','yyyy-mm-dd hh24:mi:ss')) from dual; --workhournum := round(getonedayworkhour(v_begindate,v_enddate)*24,5) ; --select (to_date(' 2022-06-02 16:58:58','yyyy-mm-dd hh24:mi:ss')-to_date('2022-06-02 16:34:21','yyyy-mm-dd hh24:mi:ss')) from dual; --select round(getonedayworkhour(to_date('2022-06-02 09:58:58','yyyy-mm-dd hh24:mi:ss'),to_date(' 2022-06-02 16:58:58','yyyy-mm-dd hh24:mi:ss'))*24,5) from dual; --select getonedayworkhour(to_date('2022-06-02 16:34:21','yyyy-mm-dd hh24:mi:ss'),to_date('2022-06-02 16:58:58','yyyy-mm-dd hh24:mi:ss'))*24 from dual;
