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. This function is useful when you need to capture the exact timestamp along with the time zone information of the current session.

Uses of Oracle CURRENT_TIMESTAMP Function

• Retrieve Current Timestamp: Get the current date and time including the time zone of the session.


• Time Zone-Sensitive Applications: Ensure that timestamps are accurate to the session's time zone.


• Precision Control: Specify the fractional seconds precision for more accurate timekeeping.


• Data Logging: Record precise timestamps for events or transactions with time zone awareness.


• Session Time Zone Adjustment: Adapt timestamps to reflect changes in the session's time zone.


• Timestamp Formatting: Format timestamps to match specific requirements, ensuring accurate data representation.

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