INTERVAL data type - MaxCompute - Alibaba Cloud Documentation Center

MaxCompute supports four date and time-related data types: DATE, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ. It also provides the INTERVAL data type, which denotes the time span between two dates or times. This topic describes the usage of the INTERVAL type.

Prerequisite

To use the INTERVAL data type, you must enable MaxCompute 2.0 data types by setting SET odps.sql.type.system.odps2=true;.

Usage instructions

The INTERVAL data type supports two types:

INTERVAL_YEAR_MONTH: Stores the year-month interval by using the YEAR and MONTH fields.
INTERVAL_DAY_TIME: Stores the day-time interval in days, hours, minutes, seconds, and fractional seconds.

INTERVAL_YEAR_MONTH

Syntax

Syntax format	Description	Example
INTERVAL '<year \| -year>-<month>' YEAR TO MONTH	Specifies both YEAR and MONTH intervals.	Interval of 120 years and 3 months: INTERVAL '120-3' YEAR TO MONTH INTERVAL '-120-3' YEAR TO MONTH
INTERVAL '<year \| -year>' YEAR	Specifies only the YEAR interval.	Interval of 9 years: INTERVAL '9' YEAR INTERVAL '-9' YEAR
INTERVAL '<month \| -month>' MONTH	Specifies only the MONTH interval.	Interval of 40 months (3 years and 4 months): INTERVAL '40' MONTH (equivalent to INTERVAL '3-4' YEAR TO MONTH) INTERVAL '-40' MONTH (equivalent to INTERVAL '-3-4' YEAR TO MONTH)

Valid parameter ranges
- year: [0, 9999].
- month: [0, 11].
Precautions
- When specifying only the MONTH interval, the month value can exceed 11, with any value exceeding 11 being converted to years for calculation. The following code shows an example:
```
SELECT INTERVAL '200' MONTH;
```
  Return result:
```
+------------+
| _c0        | 
+------------+
| 16-8       | 
+------------+
```
- When specifying both YEAR and MONTH, the month value cannot exceed 11. The following code is an incorrect example:
```
SELECT INTERVAL '-2021-12' YEAR TO MONTH;
```
  Return result:
```
FAILED: ODPS-0130161:[1,17] Parse exception - cannot parse -2021-12 as a valid INTERVAL_DAY_TIME
```

INTERVAL_DAY_TIME

Syntax

Syntax format	Description	Example
INTERVAL '<day \| -day>' DAY	Specifies only the DAY interval.	Interval of 1 day: INTERVAL '1' DAY INTERVAL '-1' DAY
INTERVAL '<hour \| -hour>' HOUR	Specifies only the HOUR interval.	Interval of 1 hour: INTERVAL '1' HOUR INTERVAL '-1' HOUR
INTERVAL '<minute \| -minute>' MINUTE	Specifies only the MINUTE interval.	Interval of 1 minute: INTERVAL '1' MINUTE INTERVAL '-1' MINUTE
INTERVAL '<second \| -second>' SECOND	Specifies only the SECOND interval.	Interval of 1.1 seconds: INTERVAL '1.1' SECOND INTERVAL '-1.1' SECOND
INTERVAL '<day \| -day> <hour>' DAY TO HOUR	Specifies both DAY and HOUR intervals.	Interval of 1 day and 23 hours: INTERVAL '1 23' DAY TO HOUR INTERVAL '-1 23' DAY TO HOUR
INTERVAL '<day \| -day> <hour>:<minute>' DAY TO MINUTE	Specifies DAY, HOUR, and MINUTE intervals simultaneously.	Interval of 1 day, 23 hours, and 59 minutes: INTERVAL '1 23:59' DAY TO MINUTE INTERVAL '-1 23:59' DAY TO MINUTE
INTERVAL '<day \| -day> <second>' DAY TO SECOND	Specify DAY, HOUR, MINUTE, and SECOND intervals simultaneously.	Interval of 1 day, 23 hours, 59 minutes, and 59.999 seconds: INTERVAL '1 23:59:59.999' DAY TO SECOND INTERVAL '-1 23:59:59.999' DAY TO SECOND
INTERVAL '<hour \| -hour>:<minute>' HOUR TO MINUTE	Specifies both HOUR and MINUTE intervals.	Interval of 23 hours and 59 minutes: INTERVAL '23:59' HOUR TO MINUTE INTERVAL '-23:59' HOUR TO MINUTE
INTERVAL '<hour \| -hour>:<minute>:<second>' HOUR TO SECOND	Specifies HOUR, MINUTE, and SECOND intervals simultaneously.	Interval of 23 hours, 59 minutes, and 59.999 seconds: INTERVAL '23:59:59.999' HOUR TO SECOND INTERVAL '-23:59:59.999' HOUR TO SECOND
INTERVAL '<minute \| -minute>:<second>' MINUTE TO SECOND	Specifies both MINUTE and SECOND intervals.	Interval of 59 minutes and 59.999 seconds: INTERVAL '59:59.999' MINUTE TO SECOND INTERVAL '-59:59.999' MINUTE TO SECOND

Valid parameter ranges
- day: [0, 2147483647].
- hour: [0, 23].
- minute: [0, 59].
- second: [0, 59.999999999].

Precautions

When specifying only the HOUR, MINUTE, or SECOND interval, the value can exceed the upper limit, and the excess will be converted to a larger unit. The following code shows an example:

SELECT INTERVAL '24' HOUR; -- Equivalent to INTERVAL '1' DAY
SELECT INTERVAL '60' MINUTE; -- Equivalent to INTERVAL '1' HOUR
SELECT INTERVAL '6000' SECOND; -- Equivalent to INTERVAL '1:40' HOUR TO MINUTE

In other usages, the value for each parameter must not exceed the upper limit. The following code is incorrect:

SELECT INTERVAL '23:60' HOUR TO MINUTE;

Return result:

FAILED: ODPS-0130161:[1,17] Parse exception - cannot parse 23:60 as a valid INTERVAL_DAY_TIME

Operations

Scenario 1: Addition and subtraction between the same type of INTERVAL

Addition and subtraction are supported in the same type of INTERVAL, but not supported between different INTERVAL_DAY_TIME and INTERVAL_YEAR_MONTH types.

Example 1: Addition between INTERVAL_DAY_TIME types.

SELECT INTERVAL '24' HOUR + INTERVAL '23' HOUR;

Return result:

+------------+
| _c0        | 
+------------+
| 1 23:00:00.000000000 | 
+------------+

Example 2: Subtraction between INTERVAL_DAY_TIME types.

SELECT INTERVAL '24' HOUR + INTERVAL '-23' HOUR;

Return result:

+------------+
| _c0        | 
+------------+
| 0 01:00:00.000000000 | 
+------------+

Example 3: Subtraction between INTERVAL_YEAR_MONTH types.

SELECT INTERVAL '5-1' YEAR TO MONTH - INTERVAL '9-2' YEAR TO MONTH;

Return result:

+------------+
| _c0        | 
+------------+
| -4-1       | 
+------------+

Example 4 (incorrect): Operations between INTERVAL_DAY_TIME and INTERVAL_YEAR_MONTH are not supported. The following code is incorrect:

SELECT INTERVAL '2000-1' YEAR TO MONTH + INTERVAL '1 23:59:59.999' DAY TO SECOND;

Return result:

FAILED: ODPS-0130071:[1,8] Semantic analysis exception - invalid operand type(s) INTERVAL_YEAR_MONTH,INTERVAL_DAY_TIME for operator '+'

Scenario 2: Subtraction of DATE, TIMESTAMP, or TIMESTAMP_NTZ types results in INTERVAL_DAY_TIME type

Note

Subtraction between different DATETIME types is not supported.

Example 1: Subtraction between two DATE types.

SELECT DATE '2021-10-29' - DATE '2024-11-29';

Return result:

+------------+
| _c0        | 
+------------+
| -1127 00:00:00.000000000 | 
+------------+

Example 2: Subtraction between two TIMESTAMP types.

SELECT TIMESTAMP '2024-11-29 00:01:10' - TIMESTAMP'2021-10-29 00:01:00';

Return result:

+------------+
| _c0        | 
+------------+
| 1127 00:00:10.000000000 | 
+------------+

Example 3 (incorrect): Subtraction between two DATETIME types is not supported. The following code is incorrect:

SELECT DATETIME '2024-11-11 00:00:00' - DATETIME'2021-11-11 00:00:00';

Return result:

FAILED: ODPS-0130071:[1,8] Semantic analysis exception - invalid operand type(s) DATETIME,DATETIME for operator '-'

Scenario 3: Addition and subtraction of DATE, TIMESTAMP, TIMESTAMP_NTZ types with INTERVAL types result in DATE, TIMESTAMP, TIMESTAMP_NTZ types

Note

Addition and subtraction between DATETIME types and INTERVAL types are not supported.

Example 1: Subtraction of a DATE type with an INTERVAL_YEAR_MONTH type.

SELECT DATE '2021-11-11' - INTERVAL '-1' MONTH;

Return result:

+------------+
| _c0        | 
+------------+
| 2021-12-11 | 
+------------+

Example 2: Subtraction of a TIMESTAMP_NTZ type from an INTERVAL_DAY_TIME type.

SELECT TIMESTAMP_NTZ'2024-11-29 00:01:10' - INTERVAL '1 23' DAY TO HOUR;

Return result:

+------------+
| _c0        |
+------------+
| 2024-11-27 01:01:10 |
+------------+

Example 3 (incorrect): Addition and subtraction between DATETIME types and INTERVAL types are not supported. The following code is incorrect:

SELECT DATETIME '2024-11-11 00:00:00' - INTERVAL '1 23' DAY TO HOUR;

Return result:

FAILED: ODPS-0130071:[1,8] Semantic analysis exception - invalid operand type(s) DATETIME,INTERVAL_DAY_TIME for operator '-'