Support Subtracting Two Date Types To Return Numeric Type

Availability

This feature is available since MogDB 5.0.0.

Introduction

This feature supports an operator that subtracts two date types and returns a numeric type that represents the difference in the number of days between the two dates.

Benefits

Enhance MogDB compatibility with Oracle to reduce application migration costs.

Description

In Oracle, two date type fields are subtracted to get a number, representing the difference in days between the two dates, and if it contains hours, minutes and seconds information, the hours, minutes and seconds are converted to decimals calculated by day.

MogDB has realized a compatible adaptation for this scenario, which is implemented and used in schema whale, and supports that two date types can be subtracted to return a number. Due to the inherent calculation method , MogDB for the numeric return precision does not do constraints , the maximum can be up to 24 decimal places.

Syntax Description

1. New operator: -

   Left operand type: date

   Right operand type: date

   Return type: numeric

   Function: Subtract two dates to return a numeric result, indicating the number of days between the two dates.

2. New bool GUC variable enable_date_operator_sub_oracle

   on: indicates that the numeric operator is used to subtract two dates.

   false: indicates that the mogdb original processing is used, and the two dates are subtracted by the Interval operator.

Example

SET enable_date_operator_sub_oracle = on;

SELECT '2022-08-09 12:12:01'::date - '2021-08-08'::date;
         ?column?         
--------------------------
 366.50834490740740740741
(1 row)

SET enable_date_operator_sub_oracle = off;

SELECT  '2022-08-09 12:12:01'::date - '2021-08-08'::date;
     ?column?      
-------------------
 366 days 12:12:01
(1 row)