Oracle TIMESTAMP data type

Learn how to use a Time stamp data type ,how to convert a Time stamp in string format to a string Timestamp format using TO_TIMESTAMP string function and TIMESTAMP literal formats. Also Learn how to get the current operating system time stamp and current session DB time stamp with example queries.

The Timestamp data type is an extension of the Date data type with an additional Fraction for a precise date storage and retrieval.

TIMESTAMP data type allows you to store date and time data including year, month, day, hour, minute and second.

In addition, it stores the fractional seconds, which is not stored by the DATE data type.

Below is the syntax for Time stamp data type declaration

column_name TIMESTAMP[(fractional_seconds_precision)]

The fractional_seconds_precision specifies the number of digits in the fractional part of the SECOND field. It ranges from 0 to 9, meaning that you can use the TIMESTAMP data type to store up to nanosecond.

If you omit the fractional_seconds_precision, it defaults to 6.

Current default timestamp format in the Oracle Database system

SELECT
  value
FROM
  V$NLS_PARAMETERS
WHERE
  parameter = 'NLS_TIMESTAMP_FORMAT';

Output

DD-MON-RR HH.MI.SSXFF AM

Let’s create a Date_demo table as shown below.

create table Date_demo
(
  id  number,     
   start_date TIMESTAMP(6)
);

Convert string to Time stamp

To convert Time stamp values that are not in the standard format, you use the TO_TIMESTAMP() function with a format string.

insert into Date_demo
values
(1,
 TO_TIMESTAMP('18-NOV-19 11:20:30.45 AM')
);

Output

ID START_DATE

1 18-NOV-19 11.20.30.450000 AM

Instead of TO_TIMESTAMP function you can also use TIMESTAMP value as literal with the following syntax

TIMESTAMP 'YYYY-MM-DD HH:MI:SS.FF'

TIMESTAMP  '2019-11-18 12:07:50.124'

insert into Date_demo
values
(1,
 TIMESTAMP  '2019-11-18 12:07:50.124'
);

Output

ID START_DATE

2 18-NOV-19 12.07.50.124000 PM

Changing the timezone

ALTER SESSION SET TIME_ZONE = '-07:00';

SELECT SESSIONTIMEZONE 
FROM DUAL;

Output

-07:00

SELECT DBTIMEZONE FROM DUAL;

Output
+05:30

Conversion functions

SYSTIMESTAMP– Return the current system date and time stamp of the operating system where the Oracle Database resides.

CURRENT_TIMESTAMP– Return the current date and time stamp in the session time zone

Example query to see the basic difference between SYSTIMESTAMP and CURRENT_TIMESTAMP.

SELECT CURRENT_TIMESTAMP CDTISTZ,/*CURRENT_DATE_TIME_STAMP_IN_SESSION_TIME_ZONE ,*/
       SYSTIMESTAMP COSDTODR /*CURRENT_OPERATING_SYSTEM_DATE_TIME_STAMP_OF_ORACLE_DATABASE_RESIDED */
  FROM DUAL;

Output

CDTISTZ COSDTODR
18-NOV-19 12.17.05.930482 AM -07:00 18-NOV-19 12.47.05.930477 PM +05:30