TIMESTAMP data type

The TIMESTAMP data type is an extension of the DATE data type, used to store year, month, day, hour, minute, and second information of the DATE data type. This topic describes the syntax, parameters, and usage of the TIMESTAMP data type.

Syntax

TIMESTAMP [(scale)]

Parameters

The range and precision of the TIMESTAMP[(scale)] data type depend on the value of scale, which can be in the range [0,9]. The maximum value is 9 (nanoseconds, i.e., seconds precise to 9 decimal places), and the minimum value is 0 (seconds, i.e., seconds precise to 0 decimal places). The default value is 6.

Considerations

The TIMESTAMP data type is commonly used to store data with high time precision that does not require time zone conversion. The value range is 0001-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999.

The default input and output format of the TIMESTAMP data type is determined by the NLS_TIMESTAMP_FORMAT parameter. Run the following SQL statement to view the default format:

SELECT @@NLS_TIMESTAMP_FORMAT FROM DUAL;

The result is as follows:

DD-MON-RR HH.MI.SSXFF AM

If you need to customize the data format, you can use conversion functions. When inserting data, you can specify the input format using the TO_TIMESTAMP(char,fmt) function. When querying data, you can specify the output format using the TO_CHAR(datetime,fmt) function. These conversion functions convert strings into the format defined by the fmt parameter. If fmt is not specified, the default format is used.

Examples

• Create two columns timestp1 and timestp2 of the TIMESTAMP data type in the Timestamp_Sample table, and specify the time precision of timestp2 as 3. Insert the date value 2020-01-01 11:00:00 into both columns using the TO_TIMESTAMP(string, format) function. The result shows that the time precision of timestp1 is default to 6 digits because no scale value was specified, while the precision of timestp2 is 3 digits after specification.

  obclient> CREATE TABLE Timestamp_Sample(timestp1 TIMESTAMP, timestp2 TIMESTAMP(3));
  Query OK, 0 rows affected
  
  obclient> 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'));
  Query OK, 1 row affected
  
  obclient> SELECT * FROM Timestamp_Sample;
  +------------------------------+---------------------------+
  | TIMESTP1                     | TIMESTP2                  |
  +------------------------------+---------------------------+
  | 01-JAN-20 11.00.00.000000 AM | 01-JAN-20 11.00.00.000 AM |
  +------------------------------+---------------------------+
  1 row in set
  

• Use the TO_CHAR (datetime,fmt) function to specify the output format.

  obclient> 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;
  +------------------------------------------------------+
  | Timestamp                                            |
  +------------------------------------------------------+
  | 2020-02-25 11:00:00.000000000 AM America/Los_Angeles |
  +------------------------------------------------------+
  1 row in set