Oracle LOCALTIMESTAMP function

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

The Oracle LOCALTIMESTAMP() function returns the current date and time in the session's time zone, with a value of datatype TIMESTAMP. Unlike CURRENT_TIMESTAMP(), it does not include the time zone offset in its output.

Uses of Oracle LOCALTIMESTAMP() Function:

• Retrieving the current local date and time: Obtain the current date and time in the session's time zone without the time zone offset.


• Providing timestamp values for logging or auditing: Generate accurate local timestamp values for tracking events within the database.


• Facilitating timezone-independent timestamp calculations: Use in scenarios where only the local time is needed without concern for timezone differences.


• Customizing fractional second precision: Adjust the fractional second precision of the timestamp output for greater accuracy.


• Session-specific time evaluations: Fetch the current time based on the session's defined time zone settings.

Syntax:

LOCALTIMESTAMP [ (timestamp_precision) ]

Parameters

Note: LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

Pictorial Presentation

Examples: Oracle LOCALTIMESTAMP () function

The following statements shows the difference between LOCALTIMESTAMP and CURRENT_TIMESTAMP:

SQL> ALTER SESSION SET TIME_ZONE = '-2:00';
Session altered.
SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

Sample Output:

  CURRENT_TIMESTAMP                      LOCALTIMESTAMP
  ---------------------------------------------------------------------------
  01-MAY-15 05.32.24.211000 AM -02:00    01-MAY-15 05.32.24.211000 AM

SQL> ALTER SESSION SET TIME_ZONE = '-5:00';
Session altered.
SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

Sample Output:

  CURRENT_TIMESTAMP                      LOCALTIMESTAMP
  ---------------------------------------------------------------------------
    01-MAY-15 02.32.46.226000 AM -05:00  01-MAY-15 02.32.46.226000 AM

Previous: LAST_DAY
Next: MONTHS_BETWEEN