You can perform multiple arithmetic operations on the following types of values: dates (
DATE), timestamps (
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
NUMBERconstants 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
NUMBERconstants 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
DATEvalue 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
DATEdata. For example, the
MONTHS_BETWEENfunction 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
DATEor 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
DATEdata. 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
DATEdata type. In this case, ApsaraDB for OceanBase converts non-
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;