Re: Time stamp diff function
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: Kim Berg Hansen <kibeha@xxxxxxxxx>
- Date: Fri, 7 Nov 2014 13:17:40 -0600
Very nice, thanks. Like I said, I don't need to calculate time intervals a
lot, and I think the last time I used the extract function I had to embed it in
a select. Thanks again.
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
>
>
>
>> 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:
