Oracle CURRENT_TIMESTAMP function

How to get the current date and time in the session time zone in Oracle?

The CURRENT_TIMESTAMP() function returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE.

Syntax:

CURRENT_TIMESTAMP [ (precision) ]

Parameters:

Note:

• The time zone offset reflects the current local time of the SQL session.
• The default precision value is 6.
• CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value.

Applies to:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Pictorial Presentation

Example: Oracle CURRENT_TIMESTAMP() function

In the following example we have used different timezones, CLS_DATE_FORMAT and display the Current timestamp and session time zone :

SQL> ALTER SESSION SET TIME_ZONE = '-2:0'; 
Session altered. 
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; 
Session altered.
SQL>  SELECT CURRENT_TIMESTAMP,SESSIONTIMEZONE FROM DUAL;

Sample Output:

CURRENT_TIMESTAMP                     SESSIONTIMEZONE
------------------------------------- -----------------
10-JUN-15 10.49.16.482000 AM -02:00   -02:00

When you use the CURRENT_TIMESTAMP with a format mask, take care that the format mask matches the value returned by the function.

The following statement fails because the mask does not include the TIME ZONE portion of the type returned by the function :

INSERT INTO current_test VALUES
(TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

The following statement uses the correct format mask to match the return type of CURRENT_TIMESTAMP :

INSERT INTO current_test VALUES 
(TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'));

Previous: CURRENT_DATE
Next: DBTIMEZONE