Re: Time stamp diff function

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Fri, 7 Nov 2014 13:32:33 -0600
Message-Id: <AAB96347-B39D-40D0-8944-529C4F251EFB_at_gmail.com>

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

>> On Fri, Nov 7, 2014 at 6:09 PM, Kim Berg Hansen <kibeha_at_gmail.com> 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_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-l

Received on Fri Nov 07 2014 - 20:32:33 CET