Tutorials to .com

Tutorials to .com » Database » Oracle » To calculate the time difference in the Oracle9i

To calculate the time difference in the Oracle9i

Print View , by: iSee ,Total views: 25 ,Word Count: 659 ,Date: Tue, 25 Aug 2009 Time: 3:59 PM

Calculate the time difference is the oracle DATA data type, a common problem. Oracle supports date calculations, you can create, such as "Date 1 - Date 2" This kind of expression to calculate the time difference between the two dates.

Once you find the time difference, you can use simple techniques to days, hours, minutes or seconds to calculate the time difference. In order to obtain the data is poor, you must choose a suitable time for units of measure, so that the data format can be hidden.

Use complete a complex transfer function to convert date is a temptation, but you will find that this is not the best solution.

round (to_number (end-date-start_date)) - elapsed time (in days) for

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

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

Show time difference of the default mode for that? To find the answer to this question, let us conduct a simple SQL * Plus queries.

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




Here, we see the Oracle to use days as a unit of elapsed time, so we can be very easy to use conversion functions to convert it to hours or minutes. However, when the number of minutes is not an integer, we will have to place the decimal point problem.


(sysdate-(sysdate-3.111)) * 1440



(SYSDATE-(SYSDATE-3.111)) * 1440



Of course, we can use ROUND function (ie, rounded function) to solve this problem, but to remember that we must first convert the DATE data type NUMBER data types.


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






We can use these functions to an elapsed time into minutes and the approximation of this value is written to Oracle tables. In this example, we have an offline (logoff) system-level trigger mechanism to calculate the time a session has already started and put it into an Oracle STATSPACK USER_LOG expanded into the form.


perfstat.stats $ user_log


elapsed_minutes =

round (to_number (logoff_time-logon_time) * 1440)


user = user_id


elapsed_minutes is NULL;

Note: The author Donald Bunleson is a 23-year experience with database administrator, who prepared the 14 databases of books and published more than 100 articles. He is also editor in chief of Oracle Internals, and he also opened Bunleson Oracle consulting firm.

(Editor: Ming Ming mingming_ky@126.com TEL: (010) 68476606)

Oracle Tutorial Articles

Can't Find What You're Looking For?

Rating: Not yet rated


No comments posted.