MaxCompute SQL日期函数

MaxCompute SQL日期函数

函数索引

函数	功能
DATEADD	按照指定的单位和幅度修改日期值。
DATE_ADD	按照指定的幅度增减天数，与date_sub的增减逻辑相反。
DATE_SUB	按照指定的幅度增减天数，与date_add的增减逻辑相反。
DATEDIFF	计算两个日期的差值并按照指定的单位表示。
DATEPART	提取日期中符合指定时间单位的字段值。
DATETRUNC	提取日期按照指定时间单位截取后的值。
FROM_UNIXTIME	将数字型的UNIX值转换为日期值。
GETDATE	获取当前系统时间。
ISDATE	判断一个日期字符串能否根据指定的格式串转换为一个日期值。
LASTDAY	获取日期所在月的最后一天。
TO_DATE	将指定格式的字符串转换为日期值。
TO_CHAR	将日期按照指定格式转换为字符串。
UNIX_TIMESTAMP	将日期转换为整型的UNIX格式的日期值。
WEEKDAY	返回日期值是当前周的第几天。
WEEKOFYEAR	返回日期值位于当年的第几周。
ADD_MONTHS	计算日期值增加指定月数后的日期。
CURRENT_TIMESTAMP	返回当前TIMESTAMP类型的时间戳。
DAY	返回日期值的天。
DAYOFMONTH	返回日部分的值。
EXTRACT	获取日期TIMESTAMP中指定单位的部分。
FROM_UTC_TIMESTAMP	将一个UTC时区的时间戳转换为一个指定时区的时间戳。
HOUR	返回日期小时部分的值。
LAST_DAY	返回日期值所在月份的最后一天日期。
MINUTE	返回日期分钟部分的值。
MONTH	返回日期值所属月份。
MONTHS_BETWEEN	返回指定日期值间的月数。
NEXT_DAY	返回大于日期值且与指定周相匹配的第一个日期。
QUARTER	返回日期值所属季度。
SECOND	返回日期秒数部分的值。
TO_MILLIS	将指定日期转换为以毫秒为单位的UNIX时间戳。
YEAR	返回日期值的年。

注意事项

在使用MaxCompute 2.0后产品扩展的部分函数时，需要执行如下语句开启新数据类型开关：

• Session级别：如果使用新数据类型，您需要在SQL语句前加上语句set odps.sql.type.system.odps2=true;，并与SQL语句一起提交执行。

• Project级别：Project Owner可根据需要对Project进行设置，等待10~15分钟后才会生效。

  setproject odps.sql.type.system.odps2=true;
  

示例数据

创建表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);

命令详解

DATEADD

说明

按照指定的单位datepart和幅度delta修改date的值。如果您需要获取在当前时间基础上指定变动幅度的日期，请结合GETDATE函数使用。

命令格式

date|datetime dateadd(date|datetime|timestamp <date>, bigint <delta>, string <datepart>)

参数说明

date：必填。日期值，DATE、DATETIME或TIMESTAMP类型。

delta：必填。修改幅度，BIGINT类型。如果delta大于0，则增，否则减。

datepart：必填。指定修改的单位，STRING类型常量。非常量、不支持的格式或其他类型会返回报错。取值遵循STRING与DATETIME类型转换的约定，即yyyy表示年，mm表示月，dd表示天。

返回值说明

返回DATE或DATETIME类型，格式为yyyy-mm-dd或yyyy-mm-dd hh:mi:ss。

示例

静态数据示例

常见用法。

--返回2005-03-01 00:00:00。加1天，结果超出当年2月份的最后一天，实际值为下个月的第1天。
select dateadd(datetime '2005-02-28 00:00:00', 1, 'dd');
--返回2005-02-27 00:00:00。减1天。
select dateadd(datetime '2005-02-28 00:00:00', -1, 'dd');
--返回2006-10-28 00:00:00。加20个月，月份溢出，年份加1。
select dateadd(datetime '2005-02-28 00:00:00', 20, 'mm');
--返回2005-03-28 00:00:00。
select dateadd(datetime '2005-02-28 00:00:00', 1, 'mm');
--返回2005-02-28 00:00:00。2005年2月没有29日，日期截取至当月最后1天。
select dateadd(datetime '2005-01-29 00:00:00', 1, 'mm');
--返回2005-02-28 00:00:00。
select dateadd(datetime '2005-03-30 00:00:00', -1, 'mm');
--返回2005-03-18。
select dateadd(date '2005-02-18', 1, 'mm');
--开启2.0新类型。此命令需要与SQL语句一起提交。返回2005-03-18 00:00:00.0。
set odps.sql.type.system.odps2=true;
select dateadd(timestamp '2005-02-18 00:00:00', 1, 'mm');
--假设当前时间为2020-11-17 16:31:44，返回2020-11-16 16:31:44。
select dateadd(getdate(),-1,'dd');
--返回NULL。
select dateadd(date '2005-02-18', 1, null);

用常量表示。

--将STRING类型常量显式转换为DATETIME类型，返回2005-02-28 00:00:00。
select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm');

输入参数为STRING类型。

--如下输入参数为STRING类型，但不符合DATETIME类型格式，会返回报错。
select dateadd('2021-08-27',1,'dd');
--如下输入参数为STRING类型，符合DATETIME类型格式，且MaxCompute项目的数据类型版本为1.0，返回2005-03-01 00:00:00。
set odps.sql.type.system.odps2=false; 
select dateadd('2005-02-28 00:00:00', 1, 'dd');

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, dateadd(date1,1,'dd') as date1_dateadd, datetime1, dateadd(datetime1,1,'mm') as datetime1_dateadd, timestamp1, dateadd(timestamp1,-1,'yyyy') as timestamp1_dateadd from mf_date_fun_t;

DATE_ADD

命令格式

date date_add(date|timestamp|string <startdate>, bigint <delta>)

命令说明

按照delta幅度增减startdate日期的天数。

参数说明

startdate：必填。起始日期值。支持DATE、DATETIME或STRING类型。

delta：必填。修改幅度。BIGINT类型。如果delta大于0，则增；delta小于0，则减；delta等于0，不增不减。

返回值说明

返回DATE类型，格式为yyyy-mm-dd。

示例

静态数据示例

--返回2005-03-01。加1天，结果超出当年2月份的最后1天，实际值为下个月的第1天。
select date_add(datetime '2005-02-28 00:00:00', 1);
--返回2005-02-27。减1天。
select date_add(date '2005-02-28', -1);
--返回2005-03-20。
set odps.sql.type