How to Calculate Time Differences in Milliseconds in Oracle?

Calculate millisecond time difference in Oracle database

In Oracle Database, calculating the millisecond time difference between two timestamps is a common task, which allows developers to accurately measure the duration or compare time-related events.

Oracle’s method

When subtracting two variables of type TIMESTAMP, Oracle returns an INTERVAL DAY TO SECOND value. This interval contains milliseconds or microseconds, depending on the database platform (Windows/Unix). By extracting the individual elements of this interval (via the EXTRACT function), developers can calculate the total number of milliseconds using the following formula:

<code class="language-sql">总毫秒数 = (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))</code>

Example calculation

Suppose we have two timestamps:

• Time stamp 1: 2023-02-06 12:45:34.123
• Time stamp 2: 2023-02-05 11:30:15.678

To calculate the time difference in milliseconds, we can use the following query:

<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 timestamp1 - timestamp2 AS diff FROM dual);</code>

This query will return the total number of milliseconds between two timestamps, in this case:

<code>total_milliseconds: 86400000 + 54000000 + 1800000 + 98543</code>

Other methods

In addition to manually calculating the total milliseconds, developers can also use Oracle's built-in functions to achieve this purpose. For example, the TO_CHAR function can be used to convert an INTERVAL DAY TO SECOND value into a string representation containing milliseconds. Alternatively, the DBMS_OUTPUT package provides the PUT_LINE procedure, allowing developers to output each component of the interval (for example, day, hour, minute, second, millisecond) separately.

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