上班时间:8:30 ~ 17:30,不包含周六周日。有点复杂,仅供参考。
-- 格式化结束时间,去掉下班(17:30)和周末。(开始时间类似做法)
create or replace function formatEndDateOnWorkDays(end_time date)
return date
is end_date date;
begin
-- 转换下班时间,转换到有效时间段
if end_time > to_date(to_char(end_time, 'yyyy-mm-dd') || ' 17:30:00', 'yyyy-mm-dd hh24:mi:ss')
then end_date := to_date(to_char(end_time+1, 'yyyy-mm-dd') || ' 8:30:00', 'yyyy-mm-dd hh24:mi:ss');
elsif end_time < to_date(to_char(end_time, 'yyyy-mm-dd') || ' 8:30:00', 'yyyy-mm-dd hh24:mi:ss')
then end_date := to_date(to_char(end_time, 'yyyy-mm-dd') || ' 8:30:00', 'yyyy-mm-dd hh24:mi:ss');
else
end_date := end_time;
end if;
-- 转换周末时间
if to_char(end_date, 'd') = '1'
then
end_date := end_date + 1;
elsif to_char(end_date, 'd') = '7'
then
end_date := end_date + 2;
end if;
return end_date;
end formatEndDateOnWorkDays;
-- 计算相隔时间(秒),去掉非工作时间
create or replace function getDelayTimeBySecond(start_time in date,
end_time in date,
date_type in varchar2)
return number
is second_ number;
holidays number(10);
temp_days number(10);
end_date date;
start_date date;
temp date;
symbol varchar2(1);
begin
if start_time is null or end_time is null
then
return 0;
end if;
end_date := formatEndDateOnWorkDays(end_time);
-- 比较精度,秒 分 时 天
if date_type = 'ss'
then
start_date := to_date(to_char(start_time, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
end_date := to_date(to_char(end_date, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
elsif date_type = 'mi'
then
start_date := to_date(to_char(start_time, 'yyyy-mm-dd hh24:mi'), 'yyyy-mm-dd hh24:mi');
end_date := to_date(to_char(end_date, 'yyyy-mm-dd hh24:mi'), 'yyyy-mm-dd hh24:mi');
elsif date_type = 'hh24'
then
start_date := to_date(to_char(start_time, 'yyyy-mm-dd hh24'), 'yyyy-mm-dd hh24');
end_date := to_date(to_char(end_date, 'yyyy-mm-dd hh24'), 'yyyy-mm-dd hh24');
elsif date_type = 'dd'
then
start_date := to_date(to_char(start_time, 'yyyy-mm-dd'), 'yyyy-mm-dd');
end_date := to_date(to_char(end_date, 'yyyy-mm-dd'), 'yyyy-mm-dd');
else
start_date := start_time;
end_date := end_date;
end if;
-- 没有超时,转换位置,防止后边进行负数计算
if (end_date - start_date) < 0
then
temp := end_date;
end_date := start_date;
start_date := temp;
symbol := '-';
else
symbol := '';
end if;
-- 如果小于9小时(一天)
if (end_date - start_date) * 24 <= 9
then
second_ := round((end_date - start_date) * 24 * 60 * 60);
else
-- 大于9小时,即过夜的,其实也就是大于15小时
-- 得到周末天数
select count(1) into holidays
from (select to_char(end_date - level, 'd') DOW from dual connect by level <= (end_date - start_date))
where DOW in (7, 1);
-- 去掉周末时间
end_date := (end_date - holidays);
-- 得到整天数
temp_days := trunc(end_date - start_date);
-- 至少大于一天又过夜(15)的
if (end_date - start_date - temp_days) > 0.625
then
-- 减去相隔天数使之小于一天,在减去过夜的15小时 + 相隔的时间,得到秒
second_ :=
round((((end_date - temp_days - (15 / 24)) - start_date) * 24 * 60 * 60) + (temp_days * 9 * 60 * 60));
else
second_ := round((((end_date - temp_days) - start_date) * 24 * 60 * 60) + (temp_days * 9 * 60 * 60));
end if;
end if;
return round(symbol||second_);
end getDelayTimeBySecond;
-- 格式化输出
create or replace function getDelayTime(start_time in date,
end_time in date,
date_type in varchar2)
return varchar2
is hour_ number(10);
minute_ number(10);
second_ number;
result varchar2(100);
symbol varchar2(1);
begin
second_ := getDelayTimeBySecond(start_time, end_time, date_type);
if second_ < 0
then
second_ := -second_;
symbol := '-';
else
symbol := '';
end if;
hour_ := trunc(second_ / 3600);
minute_ := trunc(second_ / 60) - (hour_ * 60);
second_ := second_ - (hour_ * 60 * 60) - (minute_ * 60);
result := symbol || to_char(hour_) || '小时' || to_char(minute_) || '分' ||
to_char(second_) || '秒';
return result;
end getDelayTime;