INTERVAL data type - MaxCompute - Alibaba Cloud Documentation Center

When you subtract two date or timestamp values in MaxCompute, the result is an INTERVAL — a typed representation of the time span between them. MaxCompute provides two INTERVAL subtypes: INTERVAL_YEAR_MONTH for calendar-based intervals in years and months, and INTERVAL_DAY_TIME for fixed-length intervals in days, hours, minutes, and seconds. The two subtypes are based on different time scales and cannot be combined in arithmetic operations.

Prerequisites

Before you begin, ensure that you have:

Enabled the MaxCompute 2.0 type system by running the following statement in your session:
```
SET odps.sql.type.system.odps2=true;
```

INTERVAL_YEAR_MONTH

INTERVAL_YEAR_MONTH stores an interval in years and months.

Syntax

Use the string pattern shown in the String pattern column. Prefix the pattern value with - to express a negative interval.

Syntax	String pattern	Description	Example
`INTERVAL '<value>' YEAR TO MONTH`	`y-m`	Year and month components	`INTERVAL '120-3' YEAR TO MONTH` (120 years, 3 months)
`INTERVAL '<value>' YEAR`	`y`	Year component only	`INTERVAL '9' YEAR` (9 years)
`INTERVAL '<value>' MONTH`	`m`	Month component only	`INTERVAL '40' MONTH` (40 months = 3 years and 4 months)

Valid ranges and overflow behavior

Field	Valid range	When used alone	When combined with another field
year	0–9,999	Must stay within range	Must stay within range
month	0–11	Can exceed 11 — overflow converts to years	Cannot exceed 11 — exceeding causes a parse error

Month-only overflow example

When you specify only the MONTH interval, the value can exceed 11. MaxCompute automatically converts the excess months to years:

SELECT INTERVAL '200' MONTH;

Result:

+------+
| _c0  |
+------+
| 16-8 |
+------+

200 months = 16 years and 8 months.

YEAR TO MONTH — month out of range (error)

When using YEAR TO MONTH, the month value must be between 0 and 11:

SELECT INTERVAL '-2021-12' YEAR TO MONTH;

Result:

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

INTERVAL_DAY_TIME

INTERVAL_DAY_TIME stores an interval in days, hours, minutes, seconds, and fractional seconds.

Syntax

Use the string pattern shown in the String pattern column. Prefix the pattern value with - to express a negative interval.

Syntax	String pattern	Description	Example
`INTERVAL '<value>' DAY`	`d`	Day component only	`INTERVAL '1' DAY`
`INTERVAL '<value>' HOUR`	`h`	Hour component only	`INTERVAL '1' HOUR`
`INTERVAL '<value>' MINUTE`	`m`	Minute component only	`INTERVAL '1' MINUTE`
`INTERVAL '<value>' SECOND`	`s[.nnnnnnnnn]`	Second component only (supports fractional seconds)	`INTERVAL '1.1' SECOND`
`INTERVAL '<value>' DAY TO HOUR`	`d h`	Day and hour components	`INTERVAL '1 23' DAY TO HOUR` (1 day, 23 hours)
`INTERVAL '<value>' DAY TO MINUTE`	`d h:m`	Day, hour, and minute components	`INTERVAL '1 23:59' DAY TO MINUTE`
`INTERVAL '<value>' DAY TO SECOND`	`d h:m:s`	Day, hour, minute, and second components	`INTERVAL '1 23:59:59.999' DAY TO SECOND`
`INTERVAL '<value>' HOUR TO MINUTE`	`h:m`	Hour and minute components	`INTERVAL '23:59' HOUR TO MINUTE`
`INTERVAL '<value>' HOUR TO SECOND`	`h:m:s`	Hour, minute, and second components	`INTERVAL '23:59:59.999' HOUR TO SECOND`
`INTERVAL '<value>' MINUTE TO SECOND`	`m:s`	Minute and second components	`INTERVAL '59:59.999' MINUTE TO SECOND`

Valid ranges and overflow behavior

Field	Valid range	When used alone	When combined with another field
day	0–2,147,483,647	Must stay within range	Must stay within range
hour	0–23	Can exceed 23 — overflow converts to days	Cannot exceed 23
minute	0–59	Can exceed 59 — overflow converts to hours	Cannot exceed 59
second	0–59.999999999	Can exceed 59 — overflow converts to minutes	Cannot exceed 59.999999999

Single-unit overflow example

When you specify only a single time unit, the value can exceed its upper limit. MaxCompute automatically converts the excess to the next larger unit:

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

Multi-unit value out of range (error)

When using a multi-unit format, each field must stay within its valid range:

SELECT INTERVAL '23:60' HOUR TO MINUTE;

Result:

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

Supported operations

Add or subtract INTERVAL values of the same type

Addition and subtraction work between INTERVAL values of the same type. Operations between INTERVAL_YEAR_MONTH and INTERVAL_DAY_TIME are not supported — the two types use different time scales (calendar months versus fixed-length seconds).

Add two INTERVAL_DAY_TIME values:

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

Result:

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

Subtract two INTERVAL_DAY_TIME values:

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

Result:

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

Subtract two INTERVAL_YEAR_MONTH values:

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

Result:

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

Cross-type arithmetic (error):

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

Result:

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

Subtract DATE, TIMESTAMP, or TIMESTAMP_NTZ values to get an INTERVAL_DAY_TIME

Subtracting two DATE, TIMESTAMP, or TIMESTAMP_NTZ values returns an INTERVAL_DAY_TIME result. Subtracting two DATETIME values is not supported.

Subtract two DATE values:

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

Result:

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

Subtract two TIMESTAMP values:

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

Result:

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

DATETIME subtraction (error):

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

Result:

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

Add or subtract an INTERVAL from a DATE, TIMESTAMP, or TIMESTAMP_NTZ value

Adding or subtracting an INTERVAL from a DATE, TIMESTAMP, or TIMESTAMP_NTZ value returns a value of the same type. This operation is not supported for DATETIME values.

Subtract an INTERVAL_YEAR_MONTH from a DATE:

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

Result:

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

Subtract an INTERVAL_DAY_TIME from a TIMESTAMP_NTZ:

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

Result:

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

DATETIME with INTERVAL (error):

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

Result:

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