Re: Time stamp diff function

From: William Robertson <william_at_williamrobertson.net>
Date: Fri, 7 Nov 2014 23:31:50 +0000
Message-ID: <-7703749256783216120_at_unknownmsgid>

Not to mention the fact that you can add and divide intervals but you can't sum or average them. I expect it'll be in 13c.

On 7 Nov 2014, at 19:34, Andrew Kerber <andrew.kerber_at_gmail.com> wrote:

I do admit that I am surprised Oracle doesn't include an embedded function to do this. I would think its a pretty common need.

Sent from my iPad

On Nov 7, 2014, at 11:58 AM, Kim Berg Hansen <kibeha_at_gmail.com> wrote:

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_at_gmail.com
_at_kibeha

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Nov 08 2014 - 00:31:50 CET