Calculation of dates, timestamps, and intervals

You can perform arithmetic operations on the following data types: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND. This topic describes the calculation of dates, timestamps, and intervals.

OceanBase calculates DATE and INTERVAL values based on the following rules:

• You can use the NUMBER constant in arithmetic operations for date and timestamp values instead of interval values. OceanBase Database converts a timestamp value to a date value and explains the NUMBER constant in arithmetic datetime and interval expressions as days. For example, SYSDATE +1 specifies tomorrow. SYSDATE-7 specifies one week ago. SYSDATE +(10/1440) specifies ten minutes later.

  Notice

  The date and timestamp values cannot be used in multiplication and division.

• OceanBase Database converts the BINARY_FLOAT and BINARY_DOUBLE operands to NUMBER.

• Each DATE value includes a time component, and many date operation results include a fraction. This fraction indicates a fraction of a day. For example, if the value of DATE is 1.5, it indicates 36 hours. The built-in functions of OceanBase Database return these fractions to perform common operations on the DATE data. For example, the MONTHS_BETWEEN function returns the number of months between two dates. The fractional part of the result indicates a fraction of a 31-day month.

• When an operand is a DATE or numeric value that does not include a time zone or fractional second, the calculation is performed in three cases:

  • Generally, OceanBase Database converts other operands to DATE values, with an exception that the operation where a number multiplies an interval always returns an interval.
  • If the other operand includes a time zone value, OceanBase Database uses the time zone of the current session in the returned value.
  • If the other operand includes a fractional second value, the fractional second value is discarded.

• When a timestamp, interval, or numeric value is passed to a built-in function that applies only to the DATE data type, OceanBase Database converts nonDATE values to DATE values.

• When an interval calculation returns a datetime value, the returned result must be the actual datetime value. Otherwise, an error is returned. Sample code:

  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;
  

  In the first statement, a month is added to a 31-day month. The calculation result is September 31, which is an invalid date. Therefore, the statement execution failed. In the second statement, a year is added on February 29, which appears every four years. The calculation result is invalid. Therefore, the statement execution failed. If four years are added to February 29, the calculation result is February 29, 2008, which is valid.

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