Oracle Database - TIMESTAMP data type

> Database > Oracle Database

1 - About

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

3 - Format

For a session;

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';

See NLS_TIMESTAMP_FORMAT

Advertising

4 - Management

4.1 - Diff

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
  • Otherwise:
-- 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)  )