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.

December 22, 2014 at 06:19 |

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.

December 22, 2014 at 13:24 |

Yes, I know its not quite the same, I just put that in there for a quick and easy example.