Time - Timestamp in Oracle.
Use the TIMESTAMP data type to store values that are precise to fractional seconds.
Its an extension of date that adds fractional second precision.
An application that must decide which of two events occurred first might use TIMESTAMP and not the DATE data type which store point-in-time values (dates and times).
For a session;
alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';
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, milliseconds.
- on Unix, microseconds.
If you only need a second level:
(cast(end_ts as date)-cast(start_ts as date))*24*60*60
-- Second (on milliseconds precision) -- The return value is between 0 and 60 -- * 1000 to get the milliseconds extract( second from (systimestamp - start_ts) ) -- Minute -- The return value is between 0 and 60 extract( minute from (systimestamp - start_ts) ) -- Hour -- The return value is between 0 and 24 extract( hour from (systimestamp - start_ts) ) -- Day extract( day from (systimestamp - start_ts) )