Oracle LOCALTIMESTAMP Function

The Oracle (PL/SQL) LOCALTIMESTAMP function returns the current date and time in the time zone of the current SQL session, in a value of datatype TIMESTAMP.

Note: The difference between this function and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

Syntax

/* without precision argument */
LOCALTIMESTAMP

/* with precision argument */
LOCALTIMESTAMP(precision)

Parameters

precision Optional. Specify the fractional second precision of the time value returned. Default value is 6.

Return Value

Returns the current date and time in the time zone of the current SQL session.

Example 1:

The example below shows the usage of LOCALTIMESTAMP function.

LOCALTIMESTAMP
Result: '27-DEC-2021 05.11.57.276814 AM'

LOCALTIMESTAMP(6)
Result: '27-DEC-2021 05.11.57.276814 AM'

LOCALTIMESTAMP(5)
Result: '27-DEC-2021 05.11.57.276810 AM'

LOCALTIMESTAMP(4)
Result: '27-DEC-2021 05.11.57.276800 AM'

Example 2:

The example below illustrates that LOCALTIMESTAMP is sensitive to the session time zone:

ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP FROM DUAL;

Result:
SESSIONTIMEZONE LOCALTIMESTAMP
--------------- ---------------------------------------------------
-05:00          04-APR-00 01.17.56.917550 PM -05:00


ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP FROM DUAL;

Result:
SESSIONTIMEZONE LOCALTIMESTAMP
--------------- ----------------------------------------------------
-08:00          04-APR-00 10.18.21.366065 AM -08:00

Example 3:

Consider a database table called EmployeeLogin with the following records:

EmpID	Name	Login Stamp
1	John	25-OCT-2019 09.20.38.276815 AM
2	Marry	25-OCT-2019 09.21.05.123456 AM
3	Jo	25-OCT-2019 09.24.35.654321 AM
4	Kim	25-OCT-2019 09.25.24.122433 AM
5	Ramesh	25-OCT-2019 09.27.16.556711 AM

To insert a new record in this table, the following statement can be used.

INSERT INTO EmployeeLogin 
VALUES (6, 'Suresh', LOCALTIMESTAMP);

-- see the result
SELECT * FROM EmployeeLogin;

This will produce a result similar to:

EmpID	Name	Login Stamp
1	John	25-OCT-2019 09.20.38.276815 AM
2	Marry	25-OCT-2019 09.21.05.123456 AM
3	Jo	25-OCT-2019 09.24.35.654321 AM
4	Kim	25-OCT-2019 09.25.24.122433 AM
5	Ramesh	25-OCT-2019 09.27.16.556711 AM
6	Suresh	25-OCT-2019 09.28.19.298518 AM

❮ Oracle Functions