計算兩個時間date1、date2的差值，將差值以指定的時間單位datepart表示。
命令格式
bigint datediff(date|datetime|timestamp <date1>, date|datetime|timestamp <date2>, string <datepart>)參數說明
date1、date2：必填。DATE、DATETIME或TIMESTAMP類型。被減數和減數。如果輸入為STRING類型，且MaxCompute專案的資料類型版本是1.0，則會隱式轉換為DATETIME類型後參與運算。
datepart：可選。時間單位，STRING類型常量。
如果您開啟了MaxCompute新資料類型開關，可以不填寫datepart參數，預設日期格式為天。該欄位也支援擴充的日期格式：年
-year、月-month或-mon、日-day和小時-hour。說明系統計算時會按照datepart切掉低單位部分，然後再計算結果。
傳回值說明
返回BIGINT類型。返回規則如下：
date1、date2非DATE、DATETIME或TIMESTAMP類型時，返回報錯。
如果date1小於date2，傳回值為負數。
date1或date2值為NULL時，返回NULL。
datepart值為NULL時，返回NULL。
樣本資料
為便於理解各函數的使用方法，本文為您提供來源資料，基於來源資料提供函數相關樣本。建立表mf_date_fun_t，並添加資料，命令樣本如下。
create table if not exists mf_date_fun_t(
id int,
date1 date,
datetime1 datetime,
timestamp1 timestamp,
date2 date,
datetime2 datetime,
timestamp2 timestamp,
date3 string,
date4 bigint);
insert into mf_date_fun_t values
(1,DATE'2021-11-29',DATETIME'2021-11-29 00:01:00',TIMESTAMP'2021-01-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-20',123456780),
(2,DATE'2021-11-28',DATETIME'2021-11-28 00:02:00',TIMESTAMP'2021-02-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-21',123456781),
(3,DATE'2021-11-27',DATETIME'2021-11-27 00:03:00',TIMESTAMP'2021-03-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-22',123456782),
(4,DATE'2021-11-26',DATETIME'2021-11-26 00:04:00',TIMESTAMP'2021-04-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-23',123456783),
(5,DATE'2021-11-25',DATETIME'2021-11-25 00:05:00',TIMESTAMP'2021-05-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-24',123456784),
(6,DATE'2021-11-24',DATETIME'2021-11-24 00:06:00',TIMESTAMP'2021-06-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-25',123456785),
(7,DATE'2021-11-23',DATETIME'2021-11-23 00:07:00',TIMESTAMP'2021-07-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-26',123456786),
(8,DATE'2021-11-22',DATETIME'2021-11-22 00:08:00',TIMESTAMP'2021-08-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-27',123456787),
(9,DATE'2021-11-21',DATETIME'2021-11-21 00:09:00',TIMESTAMP'2021-09-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-28',123456788),
(10,DATE'2021-11-20',DATETIME'2021-11-20 00:10:00',TIMESTAMP'2021-10-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-29',123456789);查詢表mf_date_fun_t中的資料，命令樣本如下：
select * from mf_date_fun_t;
--返回結果。
+------+-------+------------+------------+-------+------------+------------+-------+------------+
| id | date1 | datetime1 | timestamp1 | date2 | datetime2 | timestamp2 | date3 | date4 |
+------+-------+------------+------------+-------+------------+------------+-------+------------+
| 1 | 2021-11-29 | 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-20 | 123456780 |
| 2 | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-21 | 123456781 |
| 3 | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-22 | 123456782 |
| 4 | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-23 | 123456783 |
| 5 | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-24 | 123456784 |
| 6 | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-25 | 123456785 |
| 7 | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-26 | 123456786 |
| 8 | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-27 | 123456787 |
| 9 | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-28 | 123456788 |
| 10 | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-29 | 123456789 |
+------+-------+------------+------------+-------+------------+------------+-------+------------+使用樣本：待用資料樣本
--假設start=2005-12-31 23:59:59，end=2006-01-01 00:00:00，則：
--返回1。
select datediff(end, start, 'dd');
--返回1。
select datediff(end, start, 'mm');
--返回1。
select datediff(end, start, 'yyyy');
--返回1。
select datediff(end, start, 'hh');
--返回1。
select datediff(end, start, 'mi');
--返回1。
select datediff(end, start, 'ss');
--返回1800。
select datediff(datetime'2013-05-31 13:00:00', datetime'2013-05-31 12:30:00', 'ss');
--返回30。
set odps.sql.type.system.odps2=false;
select datediff('2013-05-31 13:00:00', '2013-05-31 12:30:00', 'mi');
--返回11。
select datediff(date '2013-05-21', date '2013-05-10', 'dd');
--開啟2.0新類型。此命令需要與SQL語句一起提交。返回1800。
set odps.sql.type.system.odps2=true;
select datediff(timestamp '2013-05-31 13:00:00', timestamp '2013-05-31 12:30:00', 'ss');
--假設start = 2018-06-04 19:33:23.234，end = 2018-06-04 19:33:23.250，含毫秒的日期不屬於標準DATETIME式樣，不能直接隱式轉換，此處需進行顯示轉換。返回16。
select datediff(to_date('2018-06-04 19:33:23.250', 'yyyy-mm-dd hh:mi:ss.ff3'), to_date('2018-06-04 19:33:23.234', 'yyyy-mm-dd hh:mi:ss.ff3') , 'ff3');
--返回NULL。
select datediff(date '2013-05-21', date '2013-05-10', null);
--返回NULL。
select datediff(date '2013-05-21', null, 'dd');使用樣本：表資料樣本
基於樣本資料，計算date1和date2、datetime1和datetime2、timestamp1和timestamp2列的差值，並以指定單位表示，命令樣本如下。
--開啟2.0新類型。此命令需要與SQL語句一起提交。
set odps.sql.type.system.odps2=true;
select date1, date2, datediff(date1,date2,'dd') as date1_date2_datediff, datetime1, datetime2, datediff(datetime1, datetime2,'dd') as datetime1_datetime2_datediff, timestamp1, timestamp2, datediff(timestamp1, timestamp2,'mm') as timestamp1_timestamp2_datediff from mf_date_fun_t;返回結果如下。
+------------+------------+----------------------+---------------------+---------------------+------------------------------+-------------------------------+-------------------------------+--------------------------------+
| date1 | date2 | date1_date2_datediff | datetime1 | datetime2 | datetime1_datetime2_datediff | timestamp1 | timestamp2 | timestamp1_timestamp2_datediff |
+------------+------------+----------------------+---------------------+---------------------+------------------------------+-------------------------------+-------------------------------+--------------------------------+
| 2021-11-29 | 2021-10-29 | 31 | 2021-11-29 00:01:00 | 2021-10-29 00:00:00 | 31 | 2021-01-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -9 |
| 2021-11-28 | 2021-10-29 | 30 | 2021-11-28 00:02:00 | 2021-10-29 00:00:00 | 30 | 2021-02-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -8 |
| 2021-11-27 | 2021-10-29 | 29 | 2021-11-27 00:03:00 | 2021-10-29 00:00:00 | 29 | 2021-03-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -7 |
| 2021-11-26 | 2021-10-29 | 28 | 2021-11-26 00:04:00 | 2021-10-29 00:00:00 | 28 | 2021-04-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -6 |
| 2021-11-25 | 2021-10-29 | 27 | 2021-11-25 00:05:00 | 2021-10-29 00:00:00 | 27 | 2021-05-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -5 |
| 2021-11-24 | 2021-10-29 | 26 | 2021-11-24 00:06:00 | 2021-10-29 00:00:00 | 26 | 2021-06-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -4 |
| 2021-11-23 | 2021-10-29 | 25 | 2021-11-23 00:07:00 | 2021-10-29 00:00:00 | 25 | 2021-07-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -3 |
| 2021-11-22 | 2021-10-29 | 24 | 2021-11-22 00:08:00 | 2021-10-29 00:00:00 | 24 | 2021-08-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -2 |
| 2021-11-21 | 2021-10-29 | 23 | 2021-11-21 00:09:00 | 2021-10-29 00:00:00 | 23 | 2021-09-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -1 |
| 2021-11-20 | 2021-10-29 | 22 | 2021-11-20 00:10:00 | 2021-10-29 00:00:00 | 22 | 2021-10-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | 0 |
+------------+------------+----------------------+---------------------+---------------------+------------------------------+-------------------------------+-------------------------------+--------------------------------+相關函數
DATEDIFF函數屬於日期函數，更多日期計算、日期轉換的相關函數請參見日期函數。