Search
Document Center

# Calculation of DATE and INTERVAL values

Last Updated: Jun 18, 2021

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:

• ApsaraDB for OceanBase converts the other operands to `DATE` data. The exception is the multiplication of a numeric value by an interval. In this case, an interval is returned.

• If another operand contains a time zone value, ApsaraDB for OceanBase uses the time zone of the current session in the return value.

• If another operand contains a fractional second value, the fractional second value is lost.

• 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;``