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;
