计算两个时间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.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-20 | 123456780 |
| 2 | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-21 | 123456781 |
| 3 | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-22 | 123456782 |
| 4 | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-23 | 123456783 |
| 5 | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-24 | 123456784 |
| 6 | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-25 | 123456785 |
| 7 | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-26 | 123456786 |
| 8 | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-27 | 123456787 |
| 9 | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-28 | 123456788 |
| 10 | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 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函数属于日期函数,更多日期计算、日期转换的相关函数请参见日期函数。