Re: Time stamp diff function
- From: William Robertson <william@xxxxxxxxxxxxxxxxxxxx>
- To: oracle-l <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 7 Nov 2014 23:31:50 +0000
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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx
@kibeha
Other related posts:
