Re: Time stamp diff function
- From: Kim Berg Hansen <kibeha@xxxxxxxxx>
- To: andrew.kerber@xxxxxxxxx
- Date: Fri, 7 Nov 2014 18:58:36 +0100
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
>>
>>
>>
>
Other related posts:
