oracle tips & tricks

Oracle Tips & Tricks

 
Tuesday, 22 April 2008
Computing date differences
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;

Labels:

posted by popo @ 03:13  
0 Comments:
Post a Comment
<< Home
 
 
Search

Google
Previous Post
Category
Blogrush
Blogroll
Legal
Archives
Template by
Free Blogger templates