INTERVAL YEAR TO MONTH data type

The INTERVAL YEAR TO MONTH data type stores a period of time in terms of the YEAR and MONTH fields. This data type can be used to represent the difference between two datetime values.

Syntax

INTERVAL YEAR [(precision)] TO MONTH

Parameters

precision specifies the precision of the YEAR element. The default value is 2, and the value ranges from 0 to 9. In other words, without specifying this parameter, the maximum interval that can be stored is 99 years and 11 months (which cannot exceed 100 years). If the stored parameter exceeds the default precision of 2 digits, the value of this parameter cannot be empty and must be explicitly specified.

Considerations

When inserting values of the INTERVAL YEAR TO MONTH data type, the supported formats are as follows:

For more information about how to specify values of the interval data type, see Interval literals.

Examples

Three columns of the INTERVAL YEAR TO MONTH data type, namely interval1, interval2, and interval3, are created in the Interval_Sample table, and values are inserted into the columns.

CREATE TABLE Interval_Sample (
    interval1 INTERVAL YEAR TO MONTH,
    interval2 INTERVAL YEAR(3) TO MONTH,
    interval3 INTERVAL YEAR TO MONTH
);
INSERT INTO Interval_Sample (interval1, interval2, interval3)
VALUES (INTERVAL '12-3' YEAR TO MONTH, INTERVAL '120-3' YEAR(3) TO MONTH, INTERVAL '40' MONTH);

obclient> SELECT * FROM Interval_Sample;
+-----------+-----------+-----------+
| INTERVAL1 | INTERVAL2 | INTERVAL3 |
+-----------+-----------+-----------+
| +12-03    | +120-03   | +03-04    |
+-----------+-----------+-----------+
1 row in set

Calculations with intervals and other date types

OceanBase Database supports data type conversion, so values of the interval data type can be mathematically operated with other date values. However, the database does not support arbitrary addition, subtraction, multiplication, or division operations between different data types.

For more information about the supported date type calculation matrix and data type conversion, see Calculations with DATE and INTERVAL data types and Data type conversion rules.

• Calculations between intervals return an interval data type. Example:

  obclient> SELECT INTERVAL '2-2' YEAR TO MONTH -INTERVAL '1-1' YEAR  TO MONTH calculate1, INTERVAL '2-2' YEAR TO MONTH + INTERVAL '1-1' YEAR TO MONTH calculate2 FROM DUAL;
  +---------------+---------------+
  | CALCULATE1    | CALCULATE2    |
  +---------------+---------------+
  | +000000001-01 | +000000003-03 |
  +---------------+---------------+
  1 row in set
  

• Calculations between intervals and datetime values return a datetime data type. SYSDATE returns the current time 2020-02-27 16:13:50. The following example returns the date value two months from now. The database only supports "interval + datetime", and "interval - datetime" is an invalid operation. However, both "datetime + interval" and "datetime - interval" are valid operations.

  obclient> SELECT TO_CHAR(INTERVAL '2' MONTH +SYSDATE,'YYYY-MM-DD HH24:MI:SS') calculate3 FROM DUAL;
  +---------------------+
  | CALCULATE3          |
  +---------------------+
  | 2021-12-28 17:38:41 |
  +---------------------+
  1 row in set
  

• Calculations between intervals and numbers return an interval data type. Intervals can be multiplied or divided by numbers. The following examples show the calculation of multiplying an interval of 2 months by 2 and dividing an interval of 2 days by 3.

  obclient> SELECT INTERVAL '2' MONTH*2 calculate4, INTERVAL '2' DAY/3 calculate5 FROM DUAL;
  +---------------+-------------------------------+
  | CALCULATE4    | CALCULATE5                    |
  +---------------+-------------------------------+
  | +000000000-04 | +000000000 16:00:00.000000000 |
  +---------------+-------------------------------+
  1 row in set