All Products
Search
Document Center

INTERVAL YEAR TO MONTH data type

Last Updated: Jun 18, 2021

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

Parameter

Value

Description

percision

0~9

The precision in the YEAR element. The default value is 2. That is, if you do not specify this parameter, you can store a maximum interval value of 99 years and 11 months because the maximum interval value cannot exceed 100 years. If you want to store data with the precision that exceeds the default precision of 2 digits, you must explicitly specify this parameter with a value rather than leave this parameter empty.

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.

SyntaxExampleDescription

INTERVAL 'year-month' YEAR(percision) TO MONTH

INTERVAL '120-3' YEAR(3) TO MONTH

The interval is 120 years and 3 months. Because the value of the YEAR element is greater than the default precision of 2, you must set the precision of the YEAR element to 3.

INTERVAL 'year' YEAR(percision)

INTERVAL '50' YEAR

The interval is 50 years.

INTERVAL 'month' MONTH

INTERVAL '500' MONTH

The interval is 500 months or 41 years and 8 months.

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 |
+---------------+-------------------------------+