Timestamp Comparison

Several weeks ago I determined a need to do some arithmetic based on a timestamp value on the database.  I needed to perform certain actions if the difference in the two timestamps was five minutes or greater.  This was really a fairly trivial problem, until I discovered that for some reason Oracle does not have an easy to use function to get the difference between two timestamp values.

This seemed really strange to me, so I searched thoroughly again determined that really, no such function exists.  Eventually I decided to write one of my own.  The particular problem I had only required resolution down to the minutes, but it seemed likely I might need a higher resolution in the future. Thus, I decided to write the function to allow the user to set the required resolution.  I have not done a lot of work with Oracle time and date functionality, and I ended up with the function below as my first iteration:


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;

Note that this is fairly long and repetitive.  It is fairly fast, and got the job done.  However, it did seem too repetitive so I decided to ask around.  I mailed this up to the oracle-l mailing list, asking for input on how to improve it.  Kim Berg Hansen (http://dspsd.blogspot.com/ ) came up with a much neater 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;
/

This version is very nice, neat, and short.  And that is what I decided to go with.  I made it generally available on our databases.  Calling it is very simple:

select tsdiff(ts1,ts2,units) from dual;

SQL> select tsdiff(systimestamp+1/24, systimestamp,’m’) from dual;
TSDIFF(SYSTIMESTAMP+1/24,SYSTIMESTAMP,’M’)
——————————————
59.9891503
SQL>

I thought this was a very nice solution, and while I admit it is short and easy to write yourself, it seems to me that this function is one that oracle should supply themselves.

2 Responses to “Timestamp Comparison”

  1. Kim Berg Hansen Says:

    Hi, Andrew

    Have you wondered why your example does not give exactly 60 minutes? And even isn’t consistent?😉


    SQL> select tsdiff(systimestamp+1/24, systimestamp,'m') minutes from dual;

    MINUTES
    ----------
    59.9860871

    SQL> select tsdiff(systimestamp+1/24, systimestamp,'m') minutes from dual;

    MINUTES
    ----------
    59.9870711

    SQL> select tsdiff(systimestamp+1/24, systimestamp,'m') minutes from dual;

    MINUTES
    ----------
    59.9958723

    The reason is that adding a NUMBER to a TIMESTAMP is not really supported, adding a NUMBER to a DATE is. So systimestamp+1/24 first implicitly converts systimestamp to a DATE (which looses the fractional seconds by rounding to seconds), adds 1/24th of a “day”, and then implicitly converted back to a TIMESTAMP as the expression here is used as a TIMESTAMP parameter to the function.

    But the fractional seconds have been lost, so when the diff is calculated by comparing to systimestamp WITH fractional seconds, the result is not quite exactly 60 minutes (unless I happen to press the enter key at a time that is exactly “whole seconds” with no fractions at all😉

    Using INTERVAL literals together with systimestamp, however, does NOT do implicit conversion – it stays as a TIMESTAMP datatype all the time:


    SQL> select tsdiff(systimestamp+interval '1' hour, systimestamp,'m') minutes from dual;

    MINUTES
    ----------
    60

    Be careful with TIMESTAMP arithmetic compared to DATE arithmetic – it is not the same😉 TIMESTAMP works better with INTERVAL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: