Calculating difference between two timestamps in Oracle in milliseconds

Question

How do I calculate the time difference in milliseconds between two timestamps in Oracle?

Answer 1

When you subtract two variables of type TIMESTAMP, you get an INTERVAL DAY TO SECOND which includes a number of milliseconds and/or microseconds depending on the platform. If the database is running on Windows, systimestamp will generally have milliseconds. If the database is running on Unix, systimestamp will generally have microseconds.

  1  select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' )   2*   from dual SQL> /  SYSTIMESTAMP-TO_TIMESTAMP('2012-07-23','YYYY-MM-DD') --------------------------------------------------------------------------- +000000000 14:51:04.339000000 

You can use the EXTRACT function to extract the individual elements of an INTERVAL DAY TO SECOND

SQL> ed Wrote file afiedt.buf    1  select extract( day from diff ) days,   2         extract( hour from diff ) hours,   3         extract( minute from diff ) minutes,   4         extract( second from diff ) seconds   5    from (select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) diff   6*           from dual) SQL> /        DAYS      HOURS    MINUTES    SECONDS ---------- ---------- ---------- ----------          0         14         55     37.936 

You can then convert each of those components into milliseconds and add them up

SQL> ed Wrote file afiedt.buf    1  select extract( day from diff )*24*60*60*1000 +   2         extract( hour from diff )*60*60*1000 +   3         extract( minute from diff )*60*1000 +   4         round(extract( second from diff )*1000) total_milliseconds   5    from (select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) diff   6*           from dual) SQL> /  TOTAL_MILLISECONDS ------------------           53831842 

Normally, however, it is more useful to have either the INTERVAL DAY TO SECOND representation or to have separate columns for hours, minutes, seconds, etc. rather than computing the total number of milliseconds between two TIMESTAMP values.

Answer 2

Here's a stored proc to do it:

CREATE OR REPLACE function timestamp_diff(a timestamp, b timestamp) return number is  begin   return extract (day    from (a-b))*24*60*60 +          extract (hour   from (a-b))*60*60+          extract (minute from (a-b))*60+          extract (second from (a-b)); end; / 

Up Vote if you also wanted to beat the crap out of the Oracle developer who negated to his job!

BECAUSE comparing timestamps for the first time should take everyone an hour or so...