Oracle / PLSQL: LOCALTIMESTAMP function

This article is written about how to use the Oracle/PLSQL LOCALTIMESTAMP function with syntax and examples.

Description

The Oracle/PLSQL LOCALTIMESTAMP function returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command. It returns a TIMESTAMP value.

Syntax

The syntax for the LOCALTIMESTAMP function in Oracle/PLSQL is:

LOCALTIMESTAMP

Parameters or Arguments

There are no parameters or arguments for the LOCALTIMESTAMP function.

Returns

The LOCALTIMESTAMP function returns a TIMESTAMP value.

Note

A similar function to the LOCALTIMESTAMP function is the CURRENT_TIMESTAMP function.

The difference between these two functions is that the LOCALTIMESTAMP function returns a TIMESTAMP value while the CURRENT_TIMESTAMP function returns a TIMESTAMP WITH TIME ZONE value.

Applies To

The LOCALTIMESTAMP function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example

Let’s look at some Oracle LOCALTIMESTAMP function examples and explore how to use the LOCALTIMESTAMP function in Oracle/PLSQL.

For example:

If the following ALTER SESSION command was issued:

ALTER SESSION SET TIME_ZONE = '-7:0';

And then the following SQL statement was executed:

select LOCALTIMESTAMP
from dual;

You might get the following result:

10-Sep-05 10.58.24 PM

You then modified the session time zone with the following ALTER SESSION command:

ALTER SESSION SET TIME_ZONE = '-2:0';

And then the following SQL statement was executed:

select LOCALTIMESTAMP
from dual;

You would now get the following result:

10-Sep-05 03.58.24 AM

The session time zone value has changed from -7:0 to -2:0, causing the LOCALTIMESTAMP function to return the current date and time as a value 5 hours ahead.