INTERVAL YEAR TO MONTH data type

The INTERVAL YEAR TO MONTH data type stores a period of time in YEAR and MONTH fields. A value of this data type represents the difference between two datetime values.

Syntax

INTERVAL YEAR [(precision)] TO MONTH

Parameters

precision indicates the precision of the YEAR element. The value range is [0,9], and the default value is 2. If you do not specify this parameter, the time interval can be up to 99 years and 11 months and cannot be 100 years or more. To store an interval with a year value beyond the default precision, the parameter value cannot be NULL and must be explicitly specified.

Considerations

The following table lists the formats that you can use when you insert values of the INTERVAL YEAR TO MONTH data type.

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

Examples

Create three columns interval1, interval2, and interval3 of the INTERVAL YEAR TO MONTH data type in the Interval_Sample table and insert values into the three 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

Calculation of intervals and other date types

OceanBase Database supports conversion between data types. Therefore, you can perform mathematical operations on interval values and other date values. However, OceanBase Database does not support any addition, subtraction, multiplication, and division operation on values of different data types.

For more information about the calculation matrix and conversion of the supported data types, see Calculation of dates, timestamps, and intervals and Data type conversion rules.

• The return value of the calculation on intervals is still an interval. Here is an 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
  

• The return value of the calculation on an interval and a datetime value is of the datetime type. SYSDATE returns the current time. The following example returns a date value two months after the current time. OceanBase Database supports only the operation of "Interval + Datetime", and does not support the operation of "Interval - Datetime". 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
  

• The return value from the calculation on an interval and a number is still an interval. An interval can be multiplied or divided by a number. The following example multiplies a 2-month interval by 2 and divides a 2-day interval 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