How to calculate the time difference in Oracle

How is it calculate the time lag in Oracle? Calculating the time difference is a common problem with Oracle data types. Oracle Support Date calculation, you can create an expression such as "date 1-date 2" to calculate the time difference between these two dates.

Once you discover the difference in time, you can use simple techniques to calculate the timing in terms of days, hours, minutes, or seconds. In order to get the data difference, you have to select the appropriate time unit of measurement so that you can hide the data format.

Using sophisticated transformation functions to convert dates 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)

Round (To_number (end-date-start_date) *24)-Elapsed time (in hours)

Round (To_number (end-date-start_date) *1440)-Elapsed time (in minutes)

What is the default mode for displaying the time difference? To find the answer to this question, let's do a simple SQL *plus query.

Sql> Select sysdate-(sysdate-3) from dual;

sysdate-(SYSDATE-3)
-------------------
3

Here, we see Oracle using days as a unit of elapsed time, so we can easily use the conversion function to convert it into hours or minutes. However, when the number of minutes is not an integer, we encounter the problem of placing a decimal point.

Select
(sysdate-(sysdate-3.111)) *1440
From
Dual

(sysdate-(SYSDATE-3.111)) *1440
------------------------------
4479.83333

Of course, we can use the round function to solve this problem, but remember that we must first convert the date data type to the number data type.

Select
Round (To_number (sysdate-(sysdate-3.111)) *1440)
From
Dual

ROUND (To_number (sysdate-(SYSDATE-3.111)) *1440)
----------------------------------------------
4480

We can use these functions to convert an elapsed time approximation into minutes and write this value to an Oracle table. In this example, we have an offline (logoff) system-level triggering mechanism to compute the session time that has started and put it into an 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;