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

MaxCompute：DATEDIFF