PolarDB for Oracle provides many functions that return values related to the current date and time.

All these functions return values based on the start time of the current transaction.

  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • LOCALTIMESTAMP
  • LOCALTIMESTAMP(precision)

The CURRENT_DATE function returns the current date and time based on the start time of the current transaction. If CURRENT_DATE is called multiple times within a transaction, the value of CURRENT_DATE will not change.

SELECT CURRENT_DATE FROM DUAL;

   date
-----------
 06-AUG-07    

The CURRENT_TIMESTAMP function returns the current date and time. When called from an SQL statement, this function returns the same value for each occurrence within the statement. If called from multiple statements within a transaction, this function may return different values for each occurrence. If called from a function, this function may return a different value other than the value returned by current_timestamp in the caller.

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP FROM DUAL;

                current_timestamp | current_timestamp 
----------------------------------+----------------------------------
 02-SEP-13 17:52:29.261473 +05:00 | 02-SEP-13 17:52:29.261474 +05:00 

The LOCALTIMESTAMP function can optionally be assigned a precision parameter. This parameter causes the result to be rounded to that many fractional digits in the seconds field. If no precision parameter is assigned, the result is given to the full available precision.

SELECT LOCALTIMESTAMP FROM DUAL;

       timestamp
------------------------
 06-AUG-07 16:11:35.973
(1 row)

SELECT LOCALTIMESTAMP(2) FROM DUAL;

       timestamp
-----------------------
 06-AUG-07 16:11:44.58
(1 row)

The preceding functions return the start time of the current transaction. Their values do not change during the transaction. This is considered a feature: The intent is to allow a single transaction to have a consistent notion of the "current" time. Therefore, multiple modifications within the same transaction bear the same timestamp. Other database systems can more frequently use these values.