Re: Time stamp diff function
Date: Fri, 7 Nov 2014 18:09:42 +0100
Message-ID: <CA+S=qd0xdLgUL=XdJDwX4SuN0q0QavWpZUaQesWzK44NoRk0zA_at_mail.gmail.com>
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_at_gmail.com
_at_kibeha
On Fri, Nov 7, 2014 at 3:41 PM, Andrew Kerber <andrew.kerber_at_gmail.com> 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--
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 07 2014 - 18:09:42 CET