Oracle TIMESTAMP

Summary: in this tutorial, you’ll learn about the Oracle TIMESTAMP data type and how to handle TIMESTAMP data effectively in the Oracle Database.

Introduction to Oracle TIMESTAMP data type

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

To define a TIMESTAMP column, you use the following syntax:

column_name TIMESTAMP[(fractional_seconds_precision)]
Code language: SQL (Structured Query Language) (sql)

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

If you omit the fractional_seconds_precision, it defaults to 6.

The following expression illustrates how to define a TIMESTAMP column:

...
started_at TMESTAMP(2),
...
Code language: SQL (Structured Query Language) (sql)

In this example, the started_at column is a TIMESTAMP column with fractional seconds precision sets to the microsecond.

Oracle TIMESTAMP literals

To specify TIMESTAMP literals, you use the following format:

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'
Code language: SQL (Structured Query Language) (sql)

The following example illustrates a TIMESTAMP literal:

TIMESTAMP '1999-12-31 23:59:59.10'
Code language: SQL (Structured Query Language) (sql)

For detailed information on how to construct the format string, please check the Oracle date format.

Oracle TIMESTAMP example

First, create a new table named logs that has a TIMESTAMP column for the demonstration.

CREATE TABLE logs (
    log_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    message VARCHAR(2) NOT NULL,
    logged_at TIMESTAMP (2) NOT NULL,
    PRIMARY KEY (log_id)
);
Code language: SQL (Structured Query Language) (sql)

Second, insert new rows into to the logs table:

INSERT INTO logs (
    message,
    logged_at
    )
VALUES (
    'Invalid username/password for root user',
    LOCALTIMESTAMP(2)
    );

INSERT INTO logs (
    message,
    logged_at
    )
VALUES (
    'User root logged in successfully',
    LOCALTIMESTAMP(2)
    );Code language: SQL (Structured Query Language) (sql)

In this example, we got the current local timestamp with the fractional precision seconds up to microseconds from the LOCALTIMESTAMP(2) function and inserted that value into the logged_at column of the logs table.

Third, query the TIMESTAMP data from the logs table:

SELECT log_id,
       message,
       logged_at
FROM logs;Code language: SQL (Structured Query Language) (sql)

Format TIMESTAMP values

To change the output of a TIMESTAMP value, you use the TO_CHAR() function by passing the name of TIMESTAMP value or column as the first argument, and the format string as the second argument.

The following statement uses the TO_CHAR() function to format values in the logged_at column:

SELECT message,
    TO_CHAR(logged_at, 'MONTH DD, YYYY "at" HH24:MI')
FROM logs;
Code language: SQL (Structured Query Language) (sql)

The picture below illustrates the output:

Extract TIMESTAMP components

To extract components a TIMESTAMP such as year, month, day, hour, minute, and second, you use the EXTRACT() function:

EXTRACT( component FROM timestamp);
Code language: SQL (Structured Query Language) (sql)

See the following statement as an example:

SELECT 
    message,
    EXTRACT(year FROM logged_at) year,
    EXTRACT(month FROM logged_at) month,
    EXTRACT(day FROM logged_at) day,
    EXTRACT(hour FROM logged_at) hour,
    EXTRACT(minute FROM logged_at) minute,
    EXTRACT(second FROM logged_at) second
FROM 
    logs;
Code language: SQL (Structured Query Language) (sql)

Noted that the NLS_DATE_LANGUAGE parameter determines the language for the day names (Thursday), the month names (August), and the abbreviations (THU, AUG) of TIMESTAMP.

Default TIMESTAMP format

Oracle uses the NLS_TIMESTAMP_FORMAT parameter to control the default timestamp format when a value of the character type is converted to the TIMESTAMP data type.

The following statement returns the current default timestamp format in the Oracle Database system:

SELECT
  value
FROM
  V$NLS_PARAMETERS
WHERE
  parameter = 'NLS_TIMESTAMP_FORMAT';
Code language: SQL (Structured Query Language) (sql)

Here is the output:

DD-MON-RR HH.MI.SSXFF AM
Code language: SQL (Structured Query Language) (sql)

For example, suppose you want to insert a new row into the logs table with the logged_at value as a string, you use the TO_TIMESTAMP() function to convert the string to a TIMESTAMP value as follows:

INSERT INTO logs (
    message,
    logged_at
    )
VALUES (
    'Test default Oracle timestamp format',
    TO_TIMESTAMP('03-AUG-17 11:20:30.45 AM')
    );
Code language: SQL (Structured Query Language) (sql)

Notice that the timestamp value '03-AUG-17 11:20:30.45 AM' follows the standard timestamp format.

Let’s verify the result:

SELECT
  log_id, message, logged_at
FROM
  logs;Code language: SQL (Structured Query Language) (sql)

If you want to use another timestamp format instead of the default one, you can use the ALTER SESSION SET statement to do so.

In this tutorial, you have learned about the Oracle TIMESTAMP data type and how to handle TIMESTAMP effectively.