Time stamp diff function
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: oracle-l <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 7 Nov 2014 08:41:48 -0600
I don't typically work with time intervals a lot, so I am not really familiar
with getting a difference between timestamps. I wrote the function below to
subtract two timestamps. Does anyone have a better example of how to do this:
create or replace function tsdiff(ts1 in timestamp, ts2 in timestamp, units in
varchar2)
/* units - l=millisecond s=second, m=minute, h=hour, d=day */
return number
is
diffval number;
unitsin char(1);
begin
unitsin:=lower(units);
if unitsin='l'
then
select
extract(day from (ts1-ts2))*24*60*60*1000
+ extract(hour from (ts1-ts2))*60*60*1000
+ extract(minute from (ts1-ts2))*60*1000
+ extract(second from (ts1-ts2))*1000
into diffval
from dual;
elsif unitsin='s'
then
select
extract(day from (ts1-ts2))*24*60*60
+ extract(hour from (ts1-ts2))*60*60
+ extract(minute from (ts1-ts2))*60
+ extract(second from (ts1-ts2))
into diffval
from dual;
elsif unitsin='m'
then
select
extract(day from (ts1-ts2))*24*60
+ extract(hour from (ts1-ts2))*60
+ extract(minute from (ts1-ts2))
+ extract(second from (ts1-ts2))/60
into diffval
from dual;
elsif unitsin='h'
then
select
extract(day from (ts1-ts2))*24
+ extract(hour from (ts1-ts2))
+ extract(minute from (ts1-ts2))/60
+ extract(second from (ts1-ts2))/60/60
into diffval
from dual;
elsif unitsin='d'
then
select
extract(day from (ts1-ts2))
+ extract(hour from (ts1-ts2))/24
+ extract(minute from (ts1-ts2))/24/60
+ extract(second from (ts1-ts2))/24/60/60
into diffval
from dual;
end if;
return diffval;
end;
Sent from my iPad--
//www.freelists.org/webpage/oracle-l
Other related posts:
