Oracle CURRENT_TIMESTAMP

Summary: in this tutorial, you’ll learn how to use the Oracle CURRENT_TIMESTAMP function to return the current date and time in the session time zone.

Introduction to the Oracle CURRENT_TIMESTAMP function #

The CURRENT_TIMESTAMP function returns the current timestamp with a time zone.

Here’s the the syntax of the Oracle CURRENT_TIMESTAMP function:

CURRENT_TIMESTAMP(factional_second_precision)Code language: SQL (Structured Query Language) (sql)

The CURRENT_TIMESTAMP function acccepts an optional parameter:

• factional_second_precision determines the number of fractional second precision in the returned time value. If you omit the factional_second_prevision argument, it defaults to 6.

The CURRENT_TIMESTAMP returns a value of the current timestamp in TIMESTAMP WITH TIME ZONE data type.

Oracle CURRENT_TIMESTAMP function example #

First, change the format of timestamp values to include the time components:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';Code language: SQL (Structured Query Language) (sql)

Second, show the current timestamp in the session time zone:

SELECT
  CURRENT_TIMESTAMP
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

06-AUG-17 08.26.52.742000000 PM -07:00Code language: SQL (Structured Query Language) (sql)

Let’s change the session time zone to the new one:

ALTER SESSION SET TIME_ZONE = '-08:00';Code language: SQL (Structured Query Language) (sql)

And get the current timestamp again:

SELECT
  CURRENT_TIMESTAMP
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

The current timestamp was adjusted to the new session time zone as shown below:

06-AUG-17 07.50.48.004000000 PM -08:00
Code language: SQL (Structured Query Language) (sql)

Remarks #

Notice that the CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while the LOCALTIMESTAMP returns a TIMESTAMP value without the time zone data.

Summary #

• Use the Oracle CURRENT_TIMESTAMP function to get the current timestamp in the session time zone.