Calculation of DATE and INTERVAL values

You can perform multiple arithmetic operations on the following types of values: dates (DATE), timestamps (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and intervals (INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND).

ApsaraDB for OceanBase calculates results based on the following rules:

• You can use NUMBER constants in arithmetic operations that are performed on date and timestamp values instead of interval values. In ApsaraDB for OceanBase, timestamp values are internally converted to date values and NUMBER constants in arithmetic datetime and interval expressions are interpreted as the number of days. For example, SYSDATE + 1 represents tomorrow. SYSDATE - 7 represents a week ago. SYSDATE + (10/1440) represents 10 minutes later.

  Notice

  You cannot multiply or divide date or timestamp values.

• ApsaraDB for OceanBase converts BINARY_FLOAT and BINARY_DOUBLE operands to NUMBER data.

• Each DATE value includes a time component and the results of multiple DATE operations include fractions. Each of the fractions represents a portion of a day. For example, 1.5 days is equal to 36 hours. ApsaraDB for OceanBase provides built-in functions to return these fractions for general operations on DATE data. For example, the MONTHS_BETWEEN function returns the number of months between two dates. The fractional part of the result represents the part of a 31-day month.

• If an operand is a DATE or numeric value but contains neither the time zone component nor the fractional second component, the following operations are performed:

• Assume that you pass timestamps, time intervals, or numeric values to built-in functions that apply to only the DATE data type. In this case, ApsaraDB for OceanBase converts non-DATE values to DATE values.

• When interval calculations return datetime values, the results must be actual datetime values. Otherwise, the database returns errors. For example, errors are returned for the following two statements:

SELECT TO_DATE("31-AUG-2004','DD-MON-YYYY") + TO_YMINTERVAL("0-1") FROM DUAL;
SELECT TO_DATE("29-FEB-2004','DD-MON-YYYY") + TO_YMINTERVAL("1-0") FROM DUAL;

The first statement fails because the operation of adding a month to a 31-day month results in an invalid date: September 31. The second statement fails because the operation of adding a year to a date that occurs only every four years is invalid. However, the operation of adding four years to February 29 is valid and the calculation result is February 29, 2008:

SELECT TO_DATE("29-FEB-2004", "DD-MON-YYYY") + TO_YMINTERVAL("4-0") FROM DUAL;