INTERVAL YEAR TO MONTH data type

The INTERVAL YEAR TO MONTH data type stores different objects from the DATE and TIMESTAMP data types. The TIMESTAMP data type stores specific timestamps. The DATE data type stores specific dates. The INTERVAL YEAR TO MONTH data type uses the YEAR and MONTH elements to store a period of time. This data type can be used to indicate the difference between two date and time values.

Syntax

INTERVAL YEAR [(percision)] TO MONTH

Parameter

Date formats

You can use the following formats when you insert a value of the INTERVAL YEAR TO MONTH data type. For more information about how to specify the values of interval data types, see Interval literals.

Examples

Assume that you write the following codes to create three INTERVAL YEAR TO MONTH columns named interval1 , interval2 , and interval3 in a table named Interval_Sample , and insert numeric values 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);
SELECT * FROM Interval_Sample;

Return result:

+-----------+-----------+-----------+
| interval1 | interval2 | interval3 |
+-----------+-----------+-----------+
| +12-03    | +120-03   | +03-04    |
+-----------+-----------+-----------+

Calculations between interval and other date and time types

ApsaraDB for OceanBase supports the conversion between data types. Therefore, you can perform calculations between interval values and other date values. However, the database allows you to perform addition, subtraction, multiplication, and division between data types by following rules. For more information about the matrix of calculations between date types that are currently supported, see Calculation of DATE and INTERVAL values. For more information about data type conversion, see Data type conversion.

• Example 1 : When a calculation is performed between interval values, an interval value is returned.

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;

Return result:

+---------------+----------------+
| calculate1    | calculate2     |
+--------------------------------+
| +000000001-01 | +000000003-03  |
+---------------+----------------+

• Example 2 : When a calculation is performed between interval and date and time values, a date value is returned. SYSDATE returns the current time 2020-02-27 16:13:50 . The following example returns the date value two months after the current time. The database only supports the format of an interval value plus a date and time value. The format of an interval value minus a date and time value is invalid for the calculation. However, a date time value plus an interval value and a date and time value minus an interval value are valid formats for calculations.

SELECT TO_CHAR(INTERVAL '2' MONTH +SYSDATE,'YYYY-MM-DD HH24:MI:SS') calculate3 FROM DUAL;

Return result:

+---------------------+
| calculate3          |
+---------------------+
| 2020-04-27 16:13:50 |
+---------------------+

• Example 3 : When a calculation is performed between interval and numeric values, an interval value is returned.

Interval values can be multiplied and divided with numeric values. The following example calculates the 2-month interval multiplied by 2 and the 2-day interval divided by 3.

SELECT INTERVAL '2' MONTH*2 calculate4, INTERVAL '2' DAY/3 calculate5 FROM DUAL;

The intervals of 4 months and 16 hours are returned.

+---------------+-------------------------------+
| calculate4    | calculate5                    |
+---------------+-------------------------------+
| +000000000-04 | +000000000 16:00:00.000000000 |
+---------------+-------------------------------+