Por
aquí encontre esta posible solución:
Código SQL
[-]
create or replace function time_between (start_tm in date, end_tm in date,
hours_only varchar2 default 'N') return varchar2 as
-- If "hours_only" is null or "N", the return will be a string formatted like:
-- 2 days, 3 hrs, 5 mins, 10 secs
-- If "hours_only" is not "N", then the return is a value in hours, like 102.325
ret_val varchar2(80);
start_sec number;
end_sec number;
full_sec number;
balance number;
minute number;
hour number;
days number;
--
function get_sec (time_in in date) return number as
begin
return to_number(to_char(time_in,'SSSSS'));
end;
--
begin
start_sec := get_sec(start_tm);
end_sec := get_sec(end_tm);
-- check if end time is in the same day as start time
if to_char(start_tm,'YYMMDD') = to_char(end_tm,'YYMMDD') then
full_sec := end_sec - start_sec;
days := 0;
else
days := trunc(end_tm - start_tm);
if days > 0 then
ret_val := to_char(days)||' days, ';
end if;
if end_sec > start_sec then
full_sec := end_sec - start_sec;
else
full_sec := 86400 - start_sec + end_sec;
end if;
end if;
if upper(hours_only) = 'N' then
if full_sec > 3599 then
hour := floor(full_sec / 3600);
balance := mod(full_sec,3600);
full_sec := balance;
if hour > 1 then
ret_val := ret_val||to_char(hour)||' hrs, ';
else
ret_val := ret_val||to_char(hour)||' hr, ';
end if;
end if;
if full_sec > 59 then
minute := floor(full_sec / 60);
balance := mod(full_sec,60);
full_sec := balance;
if minute > 1 then
ret_val := ret_val||to_char(minute)||' mins, ';
else
ret_val := ret_val||to_char(minute)||' min, ';
end if;
end if;
ret_val := ret_val||to_char(full_sec)||' secs';
else
-- Calculate the time difference in hours, to three decimal places
ret_val := to_char((24 * days) + round((full_sec / 3600),3));
end if;
return ret_val;
end;
/
grant execute on time_between to public;
create public synonym time_between for time_between;
Saludos!