Calculation of DATE and INTERVAL values

The DATE, TIMESTAMP, and INTERVAL data types support arithmetic operations. This topic describes the calculation rules for DATE and INTERVAL values.

OceanBase Database calculates the result based on the following rules:

• You can use NUMBER constants in arithmetic operations involving DATE and TIMESTAMP values (not INTERVAL values). OceanBase Database internally converts TIMESTAMP values to DATE values and interprets NUMBER constants in arithmetic expressions involving DATE and INTERVAL values as days. For example, SYSDATE + 1 returns tomorrow. SYSDATE - 7 returns the date seven days ago. SYSDATE + (10/1440) returns the date 10 minutes from now.

  Notice

  You cannot perform multiplication or division operations on DATE or TIMESTAMP values.

• OceanBase Database converts BINARY_FLOAT and BINARY_DOUBLE operands to NUMBER.

• Each DATE value contains a time component, and many date operations return results that include a fractional component. This fractional component represents a portion of a day. For example, 1.5 days is equivalent to 36 hours. OceanBase Database also returns these fractional components to perform common operations on DATE data. For example, the MONTHS_BETWEEN function returns the number of months between two dates. The decimal part of the result represents the portion of a 31-day month.

• If one operand is a DATE value or a numeric value, but neither contains a time zone or fractional seconds component, then:

  • OceanBase Database converts the other operand to a DATE value. The result is an INTERVAL value if the other operand is a numeric value and the operation involves multiplying or dividing by an INTERVAL value.
  • If the other operand contains a time zone value, OceanBase Database uses the current session time zone in the returned value.
  • If the other operand contains a fractional seconds value, the fractional seconds value is lost.

• When you pass a TIMESTAMP, INTERVAL, or numeric value to an Oracle Database built-in function that only accepts DATE values, OceanBase Database converts the non-DATE value to a DATE value.

• When an interval calculation returns a DATE or TIMESTAMP value, the result must be a valid DATE or TIMESTAMP value. Otherwise, the database returns an error. Here are some examples:

  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 adds one month to a date in a 31-day month, resulting in September 31, which is not a valid date, so the statement fails. The second statement adds one year to a date that only exists every four years, which is invalid, so the statement also fails. However, adding four years to February 29 is valid, resulting in February 29, 2008:

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

• The following table shows the matrix of date and time arithmetic operations. "---" indicates that the operation is not supported.