Computing date differences in Oracle9i   Donald K. Burleson

One of the confounding problems with Oracle DATE datatypes is the computation of elapsed time.

Oracle supports date arithmetic and you can make expressions like "date1 - date2" to get the difference between the two dates.  Once you have the date difference, you can use simple techniques to express the difference in days, hours, minutes or seconds.

To get the values for data differences, you must choose you unit of measurement, and this is done with the data format mask:

It might be tempting to use sophisticated conversion functions to convert a data, but we will see that this is not the most elegant solution:

round(to_number(end-date-start_date))– elapsed days

round(to_number(end-date-start_date)*24)– elapsed hours

round(to_number(end-date-start_date)*1440)– elapsed minutes

How are elapsed time data displayed by default?  To find out, we issue a simple SQL*plus query:

SQL> select sysdate-(sysdate-3) from dual;

SYSDATE-(SYSDATE-3)

-------------------

                  3

Here we see that elapsed times are expressed in days.  Hence, we can use easy conversion functions to convert this to hours or minutes:

However, when the minutes are not a whole number, we have the problem of trailing decimal places:

select

   (sysdate-(sysdate-3.111))*1440

from

   dual;

(SYSDATE-(SYSDATE-3.111))*1440

------------------------------

                    4479.83333

Of course, we can overcome this with the ROUND function, remembering that we must first convert the DATE datatype to a NUMBER:

select

   round(to_number(sysdate-(sysdate-3.111))*1440)

from

   dual;

ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440)

----------------------------------------------

                                          4480

Hence, we can use these functions to convert an elapsed time into rounded elapsed minutes, and place the value inside an Oracle table.  In this example, we have a logoff system-level trigger that computes the elapsed session time and places it inside a Oracle STATSPACK user_log extension table:

update

   perfstat.stats$user_log

set

   elapsed_minutes =

   round(to_number(logoff_time-logon_time)*1440)

where

   user = user_id

and

   elapsed_minutes is NULL;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2003_1_oracle9i_sga.htm