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

It’s similar to LOCALTIMESTAMP, except that LOCALTIMESTAMP returns a TIMESTAMP value.

Syntax

The syntax goes like this:

CURRENT_TIMESTAMP [ (precision) ]

Where the optional 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 CURRENT_TIMESTAMP
FROM DUAL;

Result:

06/AUG/21 08:32:31.165866000 AM AUSTRALIA/BRISBANE

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(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
FROM DUAL;

Result:

2021-08-06 08:44:39

Precision

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

SELECT CURRENT_TIMESTAMP(9)
FROM DUAL;

Result:

06/AUG/21 09:02:37.868264000 AM AUSTRALIA/BRISBANE

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 CURRENT_TIMESTAMP(10)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT CURRENT_TIMESTAMP(10)
FROM DUAL
Error at Command Line : 1 Column : 26
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 CURRENT_TIMESTAMP 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 CURRENT_TIMESTAMP()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT CURRENT_TIMESTAMP()
FROM DUAL
Error at Command Line : 1 Column : 26
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 CURRENT_TIMESTAMP(null)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT CURRENT_TIMESTAMP(null)
FROM DUAL
Error at Command Line : 1 Column : 26
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.