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 > > >