How to Calculate the Difference Between Two Timestamps in Oracle in Milliseconds?

Accurately calculate the millisecond difference between two timestamps in Oracle database

In Oracle database, calculating the time difference (in milliseconds) between two TIMESTAMP type variables can be achieved by direct subtraction. The result is presented as INTERVAL DAY TO SECOND, containing milliseconds or even microseconds, depending on the operating system platform.

The sytimestamp on Windows systems usually contains milliseconds, while on Unix systems it contains microseconds. For example:

select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' )
from dual;

To extract each time unit in INTERVAL DAY TO SECOND, you can use the EXTRACT function. For example:

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);

To get the total number of milliseconds, you can convert these time units to milliseconds and add:

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);

Often, however, it is more practical to keep the INTERVAL DAY TO SECOND representation or use separate columns to store different time units rather than calculating the total number of milliseconds.

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