Oracle Database - TIMESTAMP data type

Card Puncher Data Processing

About

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

Format

For a session;

alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';

See apps/search/search.jsp

Management

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





Discover More
Card Puncher Data Processing
Oracle - Date Datatype

Use the DATE data type to store point-in-time values (dates and times) in a table. An application that specifies the time for a job might use the DATE data type. The DATE data type stores: the century,...



Share this page:
Follow us:
Task Runner