PolarDB:64-bit DATE types

Precautions

• This feature is supported for clusters of minor version V1.1.29 (released in December 2022) or later. Only new clusters support this feature.
• You must use the latest version of the PolarDB JDBC driver together with this feature. The JDBC driver must be 42.2.9.1.2 or later. Otherwise, loss of precision may occur. For more information about how to download and view PolarDB-JDBC versions, see JDBC.

Overview

Compared with old versions, the new version have the following compatibility differences:

• The subtraction result of DATE values is of the NUMERIC type, instead of the INTERVAL type.
  • In old versions, the subtraction result is of the INTERVAL type.

    select pg_typeof(sysdate - sysdate) from dual;

    Sample result:

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

  • In the new version, the subtraction result is of the NUMERIC type.

    select pg_typeof(sysdate - sysdate) from dual;

    Sample result:

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

• In the new version, precision truncation is implemented for input values of higher precision.
  • In old versions, precision truncation is not implemented for input values with seconds or higher precision.

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

    Sample result:

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

  • In the new version, precision truncation is implemented for input values with seconds or higher precision.

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

    Sample result:

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

• You can set the nls_date_format parameter to specify the display format of DATE types.
  • Set the nls_date_format parameter to yyyy/mm/dd hh24:mi:ss and query the display format of DATE types.

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

    Sample result:

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

  • Set the nls_date_format parameter to yyyy-mm-dd hh24:mi:ss and query the display format of DATE types.

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

    Sample result:

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