INTERVAL数据类型 - 云原生大数据计算服务 MaxCompute

MaxCompute提供了DATE、DATETIME、TIMESTAMP及TIMESTAMP_NTZ四种日期时间相关数据类型。另外也提供代表时间段的INTERVAL数据类型，用来表示两个日期或时间之间的时间间隔。本文为您介绍INTERVAL类型的使用方法。

使用限制

使用INTERVAL数据类型时，您需要先设置SET odps.sql.type.system.odps2=true;，以打开MaxCompute 2.0数据类型。

使用说明

INTERVAL数据类型支持如下两种类型：

INTERVAL_YEAR_MONTH：表示年月间隔，使用YEAR和MONTH字段存储时间间隔。
INTERVAL_DAY_TIME：表示日间间隔，使用包括小数秒在内的天、小时、分钟和秒存储间隔。

INTERVAL_YEAR_MONTH

语法

语法格式	描述	示例
INTERVAL '<year \| -year>-<month>' YEAR TO MONTH	同时指定YEAR和MONTH间隔。	间隔120年3个月： INTERVAL '120-3' YEAR TO MONTH INTERVAL '-120-3' YEAR TO MONTH
INTERVAL '<year \| -year>' YEAR	仅指定YEAR间隔。	间隔9年： INTERVAL '9' YEAR INTERVAL '-9' YEAR
INTERVAL '<month \| -month>' MONTH	仅指定MONTH间隔。	间隔40个月（即间隔3年4个月）： INTERVAL '40' MONTH（等价于INTERVAL '3-4' YEAR TO MONTH） INTERVAL '-40' MONTH（等价于INTERVAL '-3-4' YEAR TO MONTH）

参数说明
- year：取值范围为[0, 9999]。
- month：取值范围为[0, 11]。

注意事项

仅指定MONTH间隔时，month取值可以超过11，超过部分会折算为YEAR进行计算。示例如下：
```
SELECT INTERVAL '200' MONTH;
```
返回结果：
```
+------------+
| _c0        | 
+------------+
| 16-8       | 
+------------+
```

同时指定YEAR和MONTH时，month值不能超过11。错误示例如下：

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

返回结果：

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

INTERVAL_DAY_TIME

语法

语法格式	描述	示例
INTERVAL '<day \| -day>' DAY	仅指定DAY间隔。	间隔1天： INTERVAL '1' DAY INTERVAL '-1' DAY
INTERVAL '<hour \| -hour>' HOUR	仅指定HOUR间隔。	间隔1小时： INTERVAL '1' HOUR INTERVAL '-1' HOUR
INTERVAL '<minute \| -minute>' MINUTE	仅指定MINUTE间隔。	间隔1分钟： INTERVAL '1' MINUTE INTERVAL '-1' MINUTE
INTERVAL '<second \| -second>' SECOND	仅指定SECOND间隔。	间隔1.1秒： INTERVAL '1.1' MINUTE INTERVAL '-1.1' MINUTE
INTERVAL '<day \| -day> <hour>' DAY TO HOUR	同时指定DAY和HOUR间隔	间隔1天23小时： INTERVAL '1 23' DAY TO HOUR INTERVAL '-1 23' DAY TO HOUR
INTERVAL '<day \| -day> <hour>:<minute>' DAY TO MINUTE	同时指定DAY、HOUR和MINUTE间隔。	间隔1天23小时59分钟： INTERVAL '1 23:59' DAY TO MINUTE INTERVAL '-1 23:59' DAY TO MINUTE
INTERVAL '<day \| -day> <second>' DAY TO SECOND	同时指定DAY、HOUR、MINUTE和SECOND间隔。	间隔1天23小时59分59.999秒： 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	同时指定HOUR和MINUTE间隔。	间隔23小时59分钟： INTERVAL '23:59' HOUR TO MINUTE INTERVAL '-23:59' HOUR TO MINUTE
INTERVAL '<hour \| -hour>:<minute>:<second>' HOUR TO MINUTE	同时指定HOUR、MINUTE和SECOND间隔。	间隔23小时59分59.999秒： INTERVAL '23:59:59.999' HOUR TO SECOND INTERVAL '-23:59:59.999' HOUR TO SECOND
INTERVAL '<minute \| -minute>:<second>' MINUTE TO SECOND	同时指定MINUTE和SECOND间隔。	间隔59分59.999秒： INTERVAL '59:59.999' MINUTE TO SECOND INTERVAL '-59:59.999' MINUTE TO SECOND

参数说明
- day：取值范围为[0, 2147483647]。
- hour：取值范围为[0, 23]。
- minute：取值范围为[0, 59]。
- second：取值范围为[0, 59.999999999]。

注意事项

仅指定HOUR/MINUTE/SECOND间隔时，对应参数取值可以超过范围上限，超过的部分会被折算为更大的单位。示例如下：

SELECT INTERVAL '24' HOUR; -- 等价于 INTERVAL '1' DAY
SELECT INTERVAL '60' MINUTE; -- 等价于 INTERVAL '1' HOUR
SELECT INTERVAL '6000' SECOND; -- 等价于 INTERVAL '1:40' HOUR TO MINUTE

其他用法下，对应的参数取值不能超过范围上限。错误示例如下：

SELECT INTERVAL '23:60' HOUR TO MINUTE;

返回结果：

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

运算

场景一：同类型INTERVAL之间进行加减运算

同类型INTERVAL之间可以进行加减运算，而INTERVAL_DAY_TIME和INTERVAL_YEAR_MONTH之间无法进行运算。示例如下：

示例1：INTERVAL_DAY_TIME类型之间进行加运算。

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

返回结果：

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

示例2：INTERVAL_DAY_TIME类型之间进行减运算。

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

返回结果：

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

示例3：INTERVAL_YEAR_MONTH类型之间进行减运算。

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

返回结果：

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

示例4（错误示例）：INTERVAL_DAY_TIME和INTERVAL_YEAR_MONTH之间不能进行运算，错误示例如下：

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

返回结果：

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

场景二：DATE类型、TIMESTAMP类型或TIMESTAMP_NTZ类型相减，结果为INTERVAL_DAY_TIME类型

说明

不支持DATETIME类型之间进行减运算。

示例1：两个DATE类型相减。

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

返回结果：

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

示例2：两个TIMESTAMP类型相减。

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

返回结果：

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

示例3（错误示例）：不支持两个DATETIME类型之间进行减运算，错误示例如下：

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

返回结果：

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

场景三：DATE、TIMESTAMP、TIMESTAMP_NTZ类型与INTERVAL类型相加减的结果为DATE类型、TIMESTAMP类型、TIMESTAMP_NTZ类型

说明

不支持DATETIME类型与INTERVAL类型之间进行加减运算。

示例1：DATE类型与INTERVAL_YEAR_MONTH类型相减。

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

返回结果：

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

示例2：TIMESTAMP_NTZ类型与INTERVAL_DAY_TIME类型相减。

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

返回结果：

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

示例3（错误示例）：不支持DATETIME类型与INTERVAL类型之间进行加减运算，错误示例如下：

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

返回结果：

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