In Oracle Database, the LOCALTIMESTAMP() function returns the current date and time in the session time zone in a value of data type TIMESTAMP. 

It’s similar to CURRENT_TIMESTAMP, except that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

Syntax

The syntax goes like this:

LOCALTIMESTAMP [ (timestamp_precision) ]

Where the optional timestamp_precision argument specifies the fractional second precision of the time value returned.

When calling the function without an argument, the parentheses must be omitted.

Example

Here’s an example of calling the function without specifying the precision:

SELECT LOCALTIMESTAMP
FROM DUAL;

Result:

07/AUG/21 08:49:50.026443000 AM

This example displays the date in a format based on the value of my system’s NLS_DATE_FORMAT parameter (which is currently DD/MON/RR). This parameter derives its value from the NLS_TERRITORY parameter (of which mine is AUSTRALIA).

We have the option of changing these parameters (for example, see How to Change the Date Format in your Oracle Session).

Alternatively, we can use a function like TO_CHAR() to return the result in a different format.

Example:

SELECT TO_CHAR(LOCALTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
FROM DUAL;

Result:

2021-08-07 08:50:20

Precision

Here’s an example that specifies the precision. This determines the fractional second precision of the time value returned.

SELECT LOCALTIMESTAMP(9)
FROM DUAL;

Result:

07/AUG/21 08:50:47.203717000 AM

Note that the precision argument must be a number between 0 and 9.

Here’s what happens when we pass a value outside of that range:

SELECT LOCALTIMESTAMP(10)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LOCALTIMESTAMP(10)
FROM DUAL
Error at Command Line : 1 Column : 23
Error report -
SQL Error: ORA-30088: datetime/interval precision is out of range
30088. 00000 -  "datetime/interval precision is out of range"
*Cause:    The specified datetime/interval precision was not between 0 and 9.
*Action:   Use a value between 0 and 9 for datetime/interval precision.

Omitting the Parentheses without Specifying Precision

As mentioned, when calling LOCALTIMESTAMP without passing the precision argument, you need to omit the parentheses.

Here’s what happens when we include the parentheses without specifying the precision:

SELECT LOCALTIMESTAMP()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LOCALTIMESTAMP()
FROM DUAL
Error at Command Line : 1 Column : 23
Error report -
SQL Error: ORA-30088: datetime/interval precision is out of range
30088. 00000 -  "datetime/interval precision is out of range"
*Cause:    The specified datetime/interval precision was not between 0 and 9.
*Action:   Use a value between 0 and 9 for datetime/interval precision.

We get the same error as in the previous example. Oracle Database is looking for a precision argument between 0 and 9, but we passed none.

Null Argument

The same error occurs when passing null:

SELECT LOCALTIMESTAMP(null)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LOCALTIMESTAMP(null)
FROM DUAL
Error at Command Line : 1 Column : 23
Error report -
SQL Error: ORA-30088: datetime/interval precision is out of range
30088. 00000 -  "datetime/interval precision is out of range"
*Cause:    The specified datetime/interval precision was not between 0 and 9.
*Action:   Use a value between 0 and 9 for datetime/interval precision.