使用DATEDIFF函数按指定单位计算时间差-云原生大数据计算服务 MaxCompute-阿里云

DATEDIFF函数用于计算结束日期end_date和开始日期start_date的时间差值，差值以指定的时间单位date_part表示。

命令格式

BIGINT｜INT DATEDIFF(STRING|DATE|DATETIME|TIMESTAMP|TIMESTAMP_NTZ <end_date>, STRING|DATE|DATETIME|TIMESTAMP|TIMESTAMP_NTZ <start_date>[, STRING <date_part>]

-- 标准示例。
-- 返回1。
SELECT DATEDIFF(DATE '2025-03-02', DATE '2025-03-01', 'dd');

参数说明

end_date、start_date：必填。日期或时间戳。支持STRING、DATE、DATETIME、TIMESTAMP或TIMESTAMP_NTZ类型。
如果输入为STRING类型，函数的行为取决于字符串的具体格式和MaxCompute数据类型版本。
- 如果输入的字符串包含日期格式DATE，即yyyy-mm-dd，例如2025-07-21 ，此时在MaxCompute 1.0和2.0数据类型版本下均不支持指定date_part参数。
- 如果输入的字符串为日期格式DATETIME，即yyyy-mm-dd hh:mi:ss，例如2025-07-21 00:00:00：
  - 在MaxCompute1.0数据类型版本下，会隐式转换为DATETIME类型后参与运算，并支持指定date_part参数。
  - 在MaxCompute2.0数据类型版本下，不支持隐式转换，此时不支持指定date_part参数。

date_part：可选。指定返回值的时间单位。STRING类型。若未指定，则默认为天。

支持以下取值：

时间单位	取值
年	`year`或`yyyy`
月	`month`或`mon`或`mm`
周	`week`：以周一为一周的第一天，等效于`week(monday)` `week(weekday)`：以指定的星期几`(weekday)`为一周的第一天。有效取值：周日（SUNDAY）、周一（MONDAY）、周二（TUESDAY）、周三（WEDNESDAY）、周四（THURSDAY）、周五（FRIDAY）以及周六（SATURDAY）。
ISO周	`isoweek`采用ISO 8601所规定的周边界。ISO标准下的周从周一开始算起。
日	`day`或`dd`
小时	`hour`或`hh`
分钟	`mi`
秒	`ss`
毫秒	`ff3`
微秒	`ff6`

返回值说明

返回BIGINT或INT类型。返回规则如下：

若使用date_part，则返回BIGINT类型；若未使用date_part，则返回INT类型。
end_date、start_date非STRING、DATE、DATETIME、TIMESTAMP或TIMESTAMP_NTZ类型时，返回报错。
若end_date小于start_date，返回值为负数。
end_date、start_date或date_part值为NULL时，返回NULL。
开启BigQuery兼容模式前后，入参非TIMESTAMP_NTZ类型时，函数的计算方式有所区别：
- 未开启BigQuery兼容模式时，先按单位date_part切掉低单位部分，再计算差值。例如DATE'25-01-01'与DATE'24-12-31'年差值为1，月差值为1。
- 执行 SET odps.sql.bigquery.compatible=true;命令，打开BigQuery兼容模式时：
  - date_part为年、月、周或ISO周，先按单位date_part切掉低单位部分，再计算差。例如TIMESTAMP'25-01-01'与TIMESTAMP'24-12-31'年差值为1，月差值为1。
  - date_part为日、小时、分钟、秒、毫秒或微秒，先计算总差值，再按单位date_part换算。例如TIMESTAMP'25-01-01 00:00:00'与TIMESTAMP'24-12-31 23:59:59'日差值为0，时差值为0。

使用示例

示例1：基本示例

以日为单位返回计算差值。

-- 返回1。
SELECT DATEDIFF(DATE '2025-03-02', DATE '2025-03-01', 'dd');

以小时为单位返回计算差值。

-- 返回4。
SELECT DATEDIFF(CAST('2023-10-26 14:00:00' AS DATETIME), CAST('2023-10-26 10:00:00' AS DATETIME), 'hh');

以微秒为单位返回计算差值，且end_date小于start_date。

-- 返回-86400000000。 
SELECT DATEDIFF(DATE '2025-03-01', DATE '2025-03-02', 'ff6');

以周五为周开始，以周为单位返回计算差值。

-- 返回13。 
SELECT DATEDIFF(DATETIME '2025-07-01 00:00:00', DATETIME '2025-03-28 01:30:09', 'week(friday)');

以iso周为单位返回计算差值。

-- 返回20。
SELECT DATEDIFF(TIMESTAMP_NTZ '2025-06-07 10:30:00.123', TIMESTAMP_NTZ '2025-01-15 00:30:45.123456', 'isoweek');

示例2：BigQuery兼容模式开启前后对比

-- 返回 5，默认模式，先截取再计算。
SELECT DATEDIFF(CAST('2024-11-01 10:05:05' AS TIMESTAMP), CAST('2024-11-01 10:00:10' AS TIMESTAMP), 'mi'  ) AS diff_in_minute1;

-- 返回 4，BigQuery兼容模式，先计算再截取。 
SET odps.sql.bigquery.compatible=true;
SELECT DATEDIFF(CAST('2024-11-01 10:05:05' AS TIMESTAMP), CAST('2024-11-01 10:00:10' AS TIMESTAMP), 'mi'  ) AS diff_in_minute1;

示例3：输入参数为STRING类型

如果输入end_date、start_date为STRING类型，函数的行为取决于字符串的具体格式和MaxCompute数据类型版本。

如果输入的字符串包含日期格式DATE，即yyyy-mm-dd，例如2025-07-21 ，此时在MaxCompute 1.0和2.0数据类型版本下均不支持指定date_part参数。

在MaxCompute1.0数据类型版本下：

-- 设置数据类型版本为1.0
SET odps.sql.type.system.odps2=false;
SET odps.sql.hive.compatible=false;

-- 错误示例：如下输入参数为STRING类型，日期格式为yyyy-mm-dd，且指定date_part参数，则返回NULL。
SELECT DATEDIFF('2025-07-01', '2025-06-28','day');

-- 正确示例：如下输入参数为STRING类型，日期格式为yyyy-mm-dd，且未指定date_part参数，默认date_part为天。
SELECT DATEDIFF('2025-07-01', '2025-06-28');
-- 返回结果。
+------+
| _c0  |
+------+
| 3    |
+------+

在MaxCompute2.0数据类型版本下：

-- 设置数据类型版本为2.0
SET odps.sql.type.system.odps2=true;

-- 错误示例：如下输入参数为STRING类型，日期格式为yyyy-mm-dd，且指定date_part参数，返回报错。
SELECT DATEDIFF('2025-07-01', '2025-06-28','day');

-- 正确示例：如下输入参数为STRING类型，日期格式为yyyy-mm-dd，且未指定date_part参数，默认date_part为天。
SELECT DATEDIFF('2025-07-01', '2025-06-28');
-- 返回结果。
+------+
| _c0  |
+------+
| 3    |
+------+

如果输入的字符串符合日期格式DATETIME类型，即yyyy-mm-dd hh:mi:ss，例如2025-07-21 00:00:00：

在MaxCompute1.0数据类型版本下，会隐式转换为DATETIME类型后参与运算，并支持指定date_part参数：

-- 设置数据类型版本为1.0
SET odps.sql.type.system.odps2=false;
SET odps.sql.hive.compatible=false;

-- 正确示例：如下输入参数为STRING类型，符合DATETIME类型格式。支持指定date_part参数。
SELECT DATEDIFF('2025-07-01 00:21:00', '2025-03-28 01:30:09', 'hour');
-- 返回结果。
+------------+
| _c0        |
+------------+
| 2279       |
+------------+

在MaxCompute2.0数据类型版本下，不支持隐式转换，此时不支持指定date_part参数，默认以天为单位进行差值计算和表示：

-- 设置数据类型版本为2.0
SET odps.sql.type.system.odps2=true;
SET odps.sql.hive.compatible=false;

-- 错误示例：如下输入参数为STRING类型，符合DATETIME类型格式，且指定date_part参数，返回报错。
SELECT DATEDIFF('2025-07-01 00:21:00', '2025-03-28 01:30:09', 'hour');

-- 正确示例：如下输入参数为STRING类型，不指定date_part参数，此时默认以天为单位进行差值计算。
SELECT DATEDIFF('2025-07-01 00:21:00', '2025-03-28 01:30:09');
-- 返回结果。
+------------+
| _c0        | 
+------------+
| 95         | 
+------------+