TIMESTAMP data type

As an extension of the DATE data type, the TIMESTAMP data type stores the year, month, day, hour, minute, and second values of the DATE data type. This topic describes the syntax, parameters, and considerations of the TIMESTAMP data type.

Syntax

TIMESTAMP [(scale)]

Parameters

The precision and range of TIMESTAMP [(scale)] is determined by the value of the scale parameter. The value range is [0,9]. The minimum value 0 indicates that the timestamp is accurate to seconds, the maximum value 9 indicates that the timestamp is accurate to nanoseconds, and the default value 6 indicates that the timestamp is accurate to microseconds.

Considerations

The TIMESTAMP data type is often used to store precise time data without considering the time zone changes. The value range is [0001-01-01 00:00:00.000000000,9999-12-31 23:59:59.999999999].

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

SELECT @@NLS_TIMESTAMP_FORMAT FROM DUAL;

The return result is as follows:

DD-MON-RR HH.MI.SSXFF AM

You can use a conversion function to define a data format. When you want to insert data, you can use the TO_TIMESTAMP(char,fmt) function to specify the data input format. When you want to query data, you can use the TO_CHAR(datetime,fmt) function to specify the data output format. These two conversion functions convert strings to the format specified by the fmt argument. If the fmt argument is not specified, the default format is used.

Examples

• In the Timestamp_Sample table, create columns timestp1 and timestp2 of the TIMESTAMP data type, and specify the precision of timestp2 to 3. Use TO_TIMESTAMP(string, format) to insert date value 2020-01-01 11:00:00 to both columns. As you can see from the results, the value of scale is not specified for the timestp1 column, the default scale of 6 is used. The scale of timestp2 is 3 as specified.

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