How to Calculate the Millisecond Difference Between Timestamps in Oracle?

Millisecond difference between calculated timestamps in Oracle database

Calculating the millisecond difference between two timestamps in an Oracle database requires careful consideration of platform-specific nuances as well as the required output format.

When two TIMESTAMP variables are subtracted, the result is an INTERVAL DAY TO SECOND value. This interval contains a different number of milliseconds and/or microseconds, depending on the operating system used by the database. For example, Windows-based databases typically provide milliseconds, while Unix-based databases tend to use microseconds.

Extract time component

To retrieve a specific time component from an INTERVAL DAY TO SECOND value, you can use the EXTRACT function. This function allows extraction of individual elements such as days, hours, minutes and seconds.

Calculate total milliseconds

If you need the total number of milliseconds between timestamps, you can convert each extracted component to milliseconds and sum. However, it is often more practical to keep the INTERVAL DAY TO SECOND representation or create different columns for hours, minutes, and seconds.

Example calculation:

Consider the following example:

<code class="language-sql">select systimestamp - to_timestamp('2012-07-23', 'yyyy-mm-dd')
from dual;</code>

This query retrieves the time difference as INTERVAL DAY TO SECOND value:

<code>+000000000 14:51:04.339000000</code>

Using the EXTRACT function we can extract the day, hour, minute and second components:

<code class="language-sql">select extract(day from diff) days,
       extract(hour from diff) hours,
       extract(minute from diff) minutes,
       extract(second from diff) seconds
from (select systimestamp - to_timestamp('2012-07-23', 'yyyy-mm-dd') diff
            from dual);</code>

The result is:

<code>      DAYS      HOURS    MINUTES    SECONDS
---------- ---------- ---------- ----------
         0         14         55     37.936</code>

To calculate the total milliseconds we combine these components:

<code class="language-sql">select extract(day from diff)*24*60*60*1000 +
       extract(hour from diff)*60*60*1000 +
       extract(minute from diff)*60*1000 +
       round(extract(second from diff)*1000) total_milliseconds
from (select systimestamp - to_timestamp('2012-07-23', 'yyyy-mm-dd') diff
            from dual);</code>

Return results:

<code>TOTAL_MILLISECONDS
------------------
          53831842</code>

The above is the detailed content of How to Calculate the Millisecond Difference Between Timestamps in Oracle?. For more information, please follow other related articles on the PHP Chinese website!