Hi again, Andrew Sorry, my previous take on the function had a mistake. Since I divide first and then multiply, rounding errors could occur. For example an interval of 1 hour and 15 minutes would become 75.0000000000000001 minutes. Here's a better version: 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 diff interval day(9) to second(9) := ts1 - ts2; begin return ( extract(day from diff)*24*60*60 + extract(hour from diff)*60*60 + extract(minute from diff)*60 + extract(second from diff) ) / case (lower(units)) when 'l' then 1/1000 when 's' then 1 when 'm' then 60 when 'h' then 60*60 when 'd' then 24*60*60 else null end; end; / Regards Kim Berg Hansen http://dspsd.blogspot.com kibeha@xxxxxxxxx @kibeha On Fri, Nov 7, 2014 at 6:09 PM, Kim Berg Hansen <kibeha@xxxxxxxxx> wrote: > Hi, Andres > > We have NUMTODSINTERVAL, but not DSINTERVALTONUM, sadly. > So a function like you make is probably needed, I think. > > A couple small things I can think of: > 1) There's a lot of repetition of code. > 2) Why select from dual? > > Point 1 makes not much difference performance wise. > Point 2 makes unnecessary context switches between SQL and PL/SQL and can > make a bit of performance difference. > > My take on a rewrite would be: > > 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 > diff interval day(9) to second(9) := ts1 - ts2; > begin > return ( > extract(day from diff) > + extract(hour from diff)/24 > + extract(minute from diff)/24/60 > + extract(second from diff)/24/60/60 > ) * case (lower(units)) > when 'l' then 24*60*60*1000 > when 's' then 24*60*60 > when 'm' then 24*60 > when 'h' then 24 > when 'd' then 1 > else null > end; > end; > / > > There can be other rewrites that also would be fine, but this works on an > 11.2 instance (at least with a few tests I made :-) > > I am not quite sure - but perhaps the select from dual trick might be > necessary on old versions, as PL/SQL didn't always have all SQL functions - > maybe EXTRACT used to only SQL? I am not stating that for certain, but > maybe... > > > > Regards > > > Kim Berg Hansen > > http://dspsd.blogspot.com > kibeha@xxxxxxxxx > @kibeha > > > > > > On Fri, Nov 7, 2014 at 3:41 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx> > wrote: > >> 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 >> >> >> >