How to Calculate the Millisecond Difference Between Two Oracle Timestamps?

Determining Millisecond Differences in Oracle Timestamps

Working with Oracle timestamps often requires calculating the precise time difference between two values. While second or minute differences are common, calculating the difference down to milliseconds can be crucial in specific applications.

Method

The EXTRACT function provides a solution. It extracts components from an INTERVAL DAY TO SECOND value—the result of subtracting two timestamps.

This initial query extracts the day, hour, minute, and second components:

<code class="language-sql">SELECT EXTRACT(DAY FROM diff) AS days,
       EXTRACT(HOUR FROM diff) AS hours,
       EXTRACT(MINUTE FROM diff) AS minutes,
       EXTRACT(SECOND FROM diff) AS seconds
FROM (SELECT SYSTIMESTAMP - TO_TIMESTAMP('2012-07-23', 'YYYY-MM-DD') AS diff
      FROM DUAL);</code>

To get the total milliseconds, convert these components and sum them:

<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) AS total_milliseconds
FROM (SELECT SYSTIMESTAMP - TO_TIMESTAMP('2012-07-23', 'YYYY-MM-DD') AS diff
      FROM DUAL);</code>

Important Note: The ROUND function ensures the seconds are accurately represented in milliseconds.

While calculating total milliseconds is possible, keeping the INTERVAL DAY TO SECOND result or using separate columns for each time unit is often more practical. This method, however, offers a precise millisecond calculation when needed.

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