TIMESTAMP data type

In addition to the DATE data type, the date and time data types also include the TIMESTAMP[(scale)] data type. The TIMESTAMP data type is an extension of the DATE data type. The TIMESTAMP data type stores some information that the DATE data type stores, such as the year, month, day, hour, minute, and second values. However, the TIMESTAMP data type does not store the time zone information. The highest precision of a TIMESTAMP value is nanosecond precision. Therefore, this data type is often used to store the data that has a high time precision and does not require time zone conversion.

Syntax

TIMESTAMP [(scale)]

Parameters

Format

NLS_TIMESTAMP_FORMAT determines the default input and output formats of the TIMESTAMP data type. Execute the following SQL statement to query the default format:

SELECT @@NLS_TIMESTAMP_FORMAT FROM DUAL;

The following result is returned:

DD-MON-RR HH.MI.SSXFF AM

To customize data formats, use conversion functions. When you insert data, you can use the TO_TIMESTAMP(char,fmt) function to specify the input format of the data. When you query data, you can use the TO_CHAR(datetime,fmt) function to specify the output format of the data. These two conversion functions convert a string to the format defined by the fmt parameter. If you do not specify fmt, the default format is used.

Value range

0001-01-01 00:00:00.000000000 ~ 9999-12-31 23:59:59.999999999

Examples

• Example 1 : You can run the following code to create two TIMESTAMP columns named timestp1 and timestp2 in the Timestamp_Sample table and set scale to 3 for the timestp2 column. The date value 2020-01-01 11:00:00 is inserted into both columns in the format of TO_TIMESTAMP(string,format).

CREATE TABLE Timestamp_Sample(timestp1 TIMESTAMP, timestp2 TIMESTAMP(3));
INSERT INTO Timestamp_Sample(timestp1,timestp2) VALUES(TO_TIMESTAMP('2020-01-01 11:00:00','YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP('2020-01-01 11:00:00','YYYY-MM-DD HH24:MI:SS')); 
SELECT * FROM Timestamp_Sample;

The following result is returned. The timestp1 column uses the default time precision because the value of scale is not specified. Six digits appear after the decimal point of the second value. However, in the timestp2 column, three digits appear after the decimal point of the second value because scale is set to 3.

+------------------------------+---------------------------+
| timestamp1                   | timestamp2                |
+------------------------------+---------------------------+
| 01-JAN-20 11.00.00.000000 AM | 01-JAN-20 11.00.00.000 AM |
+------------------------------+---------------------------+

• Example 2 : In the following statement, the TO_CHAR(datetime,fmt) function is used to specify the output format:

SELECT TO_CHAR(TO_TIMESTAMP_TZ('25-FEB-20 11:00:00 AM America/Los_Angeles','DD-MON-RR HH:MI:SSXFF PM TZR'),'YYYY-MM-DD HH:MI:SSXFF PM TZR') Timestamp 
FROM DUAL;

The following result is returned:

+------------------------------------------------------+
| Timestamp                                            |
+------------------------------------------------------+
| 2020-02-25 11:00:00.000000000 AM America/Los_Angeles |
+------------------------------------------------------+