Understand the behavior and compatibility of Oracle-compatible 64-bit DATE types - PolarDB

PolarDB for PostgreSQL (Compatible with Oracle) supports 64-bit DATE types that are compatible with Oracle. If you're migrating Oracle applications to PolarDB, understanding these behavioral differences helps you avoid data precision loss and application errors.

Prerequisites

Before you begin, ensure that you have:

A cluster running minor version V1.1.29 (released in December 2022) or later. Only new clusters support 64-bit DATE types. Existing clusters do not.
PolarDB JDBC driver version 42.2.9.1.2 or later. Using an older driver version may cause loss of precision. For download instructions and version information, see JDBC.

Behavioral differences from the previous version

The 64-bit DATE implementation changes two behaviors compared to the previous version. Review these changes before migrating existing applications.

DATE subtraction result type

In the new version, subtracting two DATE values returns a NUMERIC value. In the previous version, the result was an INTERVAL.

Previous version — returns INTERVAL:

select pg_typeof(sysdate - sysdate) from dual;

pg_typeof
-----------
 interval
(1 row)

New version — returns NUMERIC:

select pg_typeof(sysdate - sysdate) from dual;

pg_typeof
-----------
 numeric
(1 row)

Important

If your application processes DATE subtraction results as an INTERVAL type, update the code to handle NUMERIC values before migrating to this version.

Precision truncation

The 64-bit DATE type stores dates with 1-second resolution. Input values with sub-second precision are truncated to the nearest second.

Previous version — sub-second precision is preserved:

select to_date('2021-10-31 10:31:31.7777') - to_date('2021-10-31 10:31:31') from dual;

?column?
---------------
 00:00:00.7777
(1 row)

New version — sub-second precision is truncated:

select to_date('2021-10-31 10:31:31.7777') - to_date('2021-10-31 10:31:31') from dual;

?column?
----------
        0
(1 row)

Note

If your application relies on sub-second precision in DATE values, use the TIMESTAMP type instead, which supports up to microsecond precision.

Control the display format

Set the nls_date_format parameter to control how DATE values are displayed.

Example: slash-separated format

set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';
select sysdate from dual;

sysdate
---------------------
 2022/12/06 09:13:59
(1 row)

Example: hyphen-separated format

set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select sysdate from dual;

sysdate
---------------------
 2022-12-06 09:14:31
(1 row)