[大数据]MaxCompute SQL日期函数

MaxCompute SQL日期函数

函数索引

注意事项

在使用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.system.odps2=false; 
select date_add('2005-02-28 00:00:00', 20);
--假设当前时间为2020-11-17 16:31:44，返回2020-11-16。
select date_add(getdate(),-1);
--返回NULL。
select date_add('2005-02-28 00:00:00', null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, date_add(date1,1) as date1_date_add, datetime1, date_add(datetime1,-1) as datetime1_date_add, timestamp1, date_add(timestamp1,0) as timestamp1_date_add from mf_date_fun_t;

DATE_SUB

命令格式

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

命令说明

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

参数说明

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

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

返回值说明

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

示例

静态数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
--返回2005-02-28。减1天，实际值为上个月的最后1天。
select date_sub(datetime '2005-03-01 00:00:00', 1);
--返回2005-03-01。增1天。
select date_sub(date '2005-02-28', -1);
--返回2005-02-27。减2天。
set odps.sql.type.system.odps2=false; 
select date_sub('2005-03-01 00:00:00', 2);
--假设当前时间为2021-09-10 16:31:44，返回2020-09-09。
select date_sub(getdate(),1);
--返回NULL。
select date_sub('2005-03-01 00:00:00', null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, date_sub(date1,1) as date1_date_sub, datetime1, date_sub(datetime1,-1) as datetime1_date_sub, timestamp1, date_sub(timestamp1,0) as timestamp1_date_sub from mf_date_fun_t;

DATEDIFF

命令格式

bigint datediff(date|datetime|timestamp <date1>, date|datetime|timestamp <date2>, string <datepart>)

命令说明

计算两个时间date1、date2的差值，将差值以指定的时间单位datepart表示。

参数说明

date1、date2：必填。DATE、DATETIME或TIMESTAMP类型。被减数和减数。

datepart：可选。时间单位，STRING类型常量。

返回值说明

返回BIGINT类型。

示例

静态数据示例

--假设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');

表数据示例

--开启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;

DATEPART

命令格式

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

命令说明

提取日期date中符合指定时间单位datepart的值。

参数说明

date：必填。DATE、DATETIME或TIMESTAMP类型。

datepart：必填。STRING类型常量，支持扩展的日期格式。

返回值说明

返回BIGINT类型。

示例

静态数据示例

--返回2013。
select datepart(datetime'2013-06-08 01:10:00', 'yyyy'); 
--返回6。
select datepart(datetime'2013-06-08 01:10:00', 'mm');
--返回2013。
select datepart(date '2013-06-08', 'yyyy');
--开启2.0新类型。此命令需要与SQL语句一起提交。返回2013。
set odps.sql.type.system.odps2=true;
select datepart(timestamp '2013-06-08 01:10:00', 'yyyy');
--返回2013。
set odps.sql.type.system.odps2=false;
select datepart('2013-06-08 01:10:00', 'yyyy');
--返回NULL。
select datepart(date '2013-06-08', null);

表数据示例

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

DATETRUNC

命令格式

date|datetime datetrunc (date|datetime|timestamp <date>, string <datepart>)

命令说明

返回将日期date按照datepart指定的时间单位进行截取后的日期值。

参数说明

date：必填。DATE、DATETIME或TIMESTAMP类型。

datepart：必填。STRING类型常量。支持扩展的日期格式。

返回值说明

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

示例

静态数据示例

--返回2011-01-01 00:00:00。
select datetrunc(datetime'2011-12-07 16:28:46', 'yyyy');
--返回2011-12-01 00:00:00。
select datetrunc(datetime'2011-12-07 16:28:46', 'month');
--返回2011-12-07 00:00:00。
select datetrunc(datetime'2011-12-07 16:28:46', 'DD');
--返回2011-01-01。
select datetrunc(date '2011-12-07', 'yyyy');
--开启2.0新类型。此命令需要与SQL语句一起提交。返回2011-01-01 00:00:00.0。
set odps.sql.type.system.odps2=true;
select datetrunc(timestamp '2011-12-07 16:28:46', 'yyyy');
--返回2011-01-01 00:00:00.0。
set odps.sql.type.system.odps2=false;
select datetrunc('2011-12-07 16:28:46', 'yyyy');
--返回NULL。
select datetrunc(date '2011-12-07', null);

表数据示例

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

FROM_UNIXTIME

命令格式

datetime from_unixtime(bigint <unixtime>)

命令说明

将数字型的UNIX时间日期值unixtime转为日期值。

参数说明

unixtime：必填。BIGINT类型，秒数，UNIX格式的日期时间值。

返回值说明

返回DATETIME类型，格式为yyyy-mm-dd hh:mi:ss。unixtime值为NULL时，返回NULL。

示例

静态数据示例

--返回1973-11-30 05:33:09。
select from_unixtime(123456789);
--返回1973-11-30 05:33:09。
set odps.sql.type.system.odps2=false;
select from_unixtime('123456789');
--返回NULL。
select from_unixtime(null);

表数据示例

select date4, from_unixtime(date4) as date4_from_unixtime from mf_date_fun_t;

GETDATE

命令格式

datetime getdate()

命令说明

获取当前系统时间。使用东八区时间作为MaxCompute标准时间。

返回值说明

返回当前日期和时间，DATETIME类型。

ISDATE

命令格式

boolean isdate(string <date>, string <format>)

命令说明

判断一个日期字符串能否根据指定的格式串转换为一个日期值。如果能转换成功，返回True；否则返回False。

参数说明

date：必填。STRING类型。

format：必填。STRING类型常量，不支持日期扩展格式。

返回值说明

返回BOOLEAN类型。date或format值为NULL时，返回NULL。

示例

静态数据示例

--返回True。
select isdate('2021-10-11','yyyy-mm-dd');
--返回false。
set odps.sql.type.system.odps2=false;
select isdate(1678952314,'yyyy-mm-dd');

表数据示例

select date3, isdate(date3,'yyyy-mm-dd') as date3_isdate from mf_date_fun_t;

LASTDAY

命令格式

datetime lastday(datetime <date>)

命令说明

取date所在月的最后一天，截取到天，时分秒部分为00:00:00。

参数说明

date：DATETIME类型日期值，格式为yyyy-mm-dd hh:mi:ss。

返回值说明

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

示例

静态数据示例

--返回2013-06-30 00:00:00。
select lastday (datetime '2013-06-08 01:10:00');
--返回2013-06-30 00:00:00。
set odps.sql.type.system.odps2=false;
select lastday ('2013-06-08 01:10:00');
--返回NULL。
select lastday (null);

表数据示例

select datetime1, lastday(datetime1) as datetime1_lastday from mf_date_fun_t;

TO_DATE

命令格式

datetime to_date(string <date>, string <format>)

命令说明

将date转换成符合format格式的日期值。

参数说明

date：必填。STRING类型，要转换的字符串格式的日期值。

format：必填。STRING类型常量，日期格式。yyyy为4位数的年，mm为2位数的月，dd为2位数的日，hh为24小时制的时，mi为2位数的分钟，ss为2位数秒，ff3为3位精度毫秒。

返回值说明

返回DATETIME类型，格式为yyyy-mm-dd hh:mi:ss。date或format值为NULL时，返回NULL。

示例

静态数据示例

--返回2010-12-03 00:00:00。
select to_date('阿里巴巴2010-12*03', '阿里巴巴yyyy-mm*dd');
--返回2008-07-18 00:00:00。
select to_date('20080718', 'yyyymmdd');
--返回2008-07-18 20:30:00。
select to_date('200807182030','yyyymmddhhmi');
--'2008718'无法转为标准日期值，引发异常，应该为'20080718'。
select to_date('2008718', 'yyyymmdd');
--'阿里巴巴2010-12*3'无法转为标准日期值，引发异常，应该为'阿里巴巴2010-12*03'。
select to_date('阿里巴巴2010-12*3', '阿里巴巴yyyy-mm*dd');
--'2010-24-01'无法转为标准日期值，引发异常，应该为'2010-01-24'。
select to_date('2010-24-01', 'yyyy');
--返回2018-10-30 15:13:12.
select to_date('20181030 15-13-12.345','yyyymmdd hh-mi-ss.ff3');
--返回NULL。
select to_date(null,'yyyymmdd hh-mi-ss.ff3');
--返回NULL。
select to_date('20181030 15-13-12.345',null);

表数据示例

select date3, to_date(date3, 'yyyy-mm-dd') as date3_to_date from mf_date_fun_t;

TO_CHAR

命令格式

string to_char(datetime <date>, string <format>)

命令说明

将日期类型date按照format指定的格式转成字符串。

参数说明

date：必填。DATETIME类型日期值，格式为yyyy-mm-dd hh:mi:ss。

format：必填。STRING类型常量。format中的日期格式部分会被替换成相应的数据，其他字符直接输出。

返回值说明

返回STRING类型。

示例

静态数据示例

--返回阿里金融2010-12*03。需要注意在Windows环境的MaxCompute客户端运行时返回结果中的中文字符显示存在问题。
select to_char(datetime'2010-12-03 00:00:00', '阿里金融yyyy-mm*dd');
--返回20080718。
select to_char(datetime'2008-07-18 00:00:00', 'yyyymmdd');
--返回20080718。
set odps.sql.type.system.odps2=false;
select to_char('2008-07-18 00:00:00', 'yyyymmdd');
--'阿里巴巴2010-12*3'无法转换为符合要求的格式，会引发异常，应该为'阿里巴巴2010-12*03'。
select to_char(datetime'阿里巴巴2010-12*3', '阿里巴巴yyyy-mm*dd');
--'2010-24-01'不是标准日期值，引发异常，应该为'2010-01-24'。
select to_char(datetime'2010-24-01', 'yyyy');
--'2008718'不是标准日期值，引发异常，应该为'20080718'。
select to_char(datetime'2008718', 'yyyymmdd');
--返回NULL。
select to_char(datetime'2010-12-03 00:00:00', null);

表数据示例

select datetime1, to_char(datetime1, 'yyyy-mm-dd') as datetime1_to_char from mf_date_fun_t;

UNIX_TIMESTAMP

命令格式

bigint unix_timestamp(datetime|date|timestamp|string <date>)

命令说明

将日期date转化为整型的UNIX格式的日期时间值。

参数说明

date：必填。DATETIME、DATE、TIMESTAMP或STRING类型日期值，格式为yyyy-mm-dd、yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回BIGINT类型，表示UNIX格式日期值。

示例

静态数据示例

--返回1237518660。
select unix_timestamp(datetime'2009-03-20 11:11:00'); 
--返回1237518660。
set odps.sql.type.system.odps2=false;
select unix_timestamp('2009-03-20 11:11:00'); 
--返回NULL。
select unix_timestamp(null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, unix_timestamp(date1) as date1_unix_timestamp, datetime1, unix_timestamp(datetime1) as datetime1_unix_timestamp, timestamp1, unix_timestamp(timestamp1) as timestamp1_unix_timestamp from mf_date_fun_t;

WEEKDAY

命令格式

bigint weekday (datetime <date>)

命令说明

返回date日期是当前周的第几天。

参数说明

date：必填。DATETIME类型日期值。格式为yyyy-mm-dd hh:mi:ss。

返回值说明

返回BIGINT类型。

示例

静态数据示例

--返回4。
select weekday (datetime '2009-03-20 11:11:00');
--返回4。
set odps.sql.type.system.odps2=false;
select weekday ('2009-03-20 11:11:00');
--返回NULL。
select weekday (null);

表数据示例

select datetime1, weekday(datetime1) as datetime1_weekday from mf_date_fun_t;

WEEKOFYEAR

命令格式

bigint weekofyear (datetime <date>)

命令说明

返回日期date位于那一年的第几周。周一作为一周的第一天。

参数说明

date：必填。DATETIME类型日期值。格式为yyyy-mm-dd hh:mi:ss。

返回值说明

返回BIGINT类型。

示例

静态数据示例

--返回1。虽然20141229属于2014年，但是这一周的大多数日期是在2015年，因此返回结果为1，表示是2015年的第一周。
select weekofyear(to_date("20141229", "yyyymmdd"));  
--返回1。    
select weekofyear(to_date("20141231", "yyyymmdd")); 
--返回53。 
select weekofyear(to_date("20151229", "yyyymmdd"));
--返回48。
set odps.sql.type.system.odps2=false;
select weekofyear('2021-11-29 00:01:00');
--返回NULL。
select weekofyear('20141231');
--返回NULL。
select weekofyear(null);

表数据示例

select datetime1, weekofyear(datetime1) as datetime1_weekofyear from mf_date_fun_t;

ADD_MONTHS

命令格式

string add_months(date|datetime|timestamp|string <startdate>, int <num_months>)

命令说明

返回开始日期startdate增加num_months个月后的日期。

参数说明

startdate：必填。DATE、DATETIME、TIMESTAMP或STRING类型，格式为yyyy-mm-dd、yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

num_months：必填。INT型数值。

返回值说明

返回STRING类型的日期值，格式为yyyy-mm-dd。

示例

静态数据示例

--返回2017-05-14。
select add_months('2017-02-14',3);
--返回0017-05-14。
select add_months('17-2-14',3);
--返回2017-05-14。
select add_months('2017-02-14 21:30:00',3);
--返回NULL。
select add_months('20170214',3);
--返回NULL。
select add_months('2017-02-14 21:30:00',null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, add_months(date1,1) as date1_add_months, datetime1, add_months(datetime1, 2) as datetime1_add_months, timestamp1, add_months(timestamp1,3) as timestamp1_add_months, date3, add_months(date3,4) as date3_add_months from mf_date_fun_t;

CURRENT_TIMESTAMP

命令格式

timestamp current_timestamp()

命令说明

返回当前TIMESTAMP类型的时间戳，值不固定。

返回值说明

返回TIMESTAMP类型。

示例

--返回'2017-08-03 11:50:30.661'。
set odps.sql.type.system.odps2=true;
select current_timestamp(); 

DAY

命令格式

int day(datetime|timestamp|date|string <date>)

命令说明

返回一个日期的天。

参数说明

date：必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值，格式为yyyy-mm-dd、yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回INT类型。

示例

静态数据示例

--返回1。
select day('2014-09-01');
--返回NULL。
select day('20140901');
--返回NULL。
select day(null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, day(date1) as date1_day, datetime1, day(datetime1) as datetime1_day, timestamp1, day(timestamp1) as timestamp1_day, date3, day(date3) as date3_day from mf_date_fun_t;

DAYOFMONTH

命令格式

int dayofmonth(datetime|timestamp|date|string <date>)

命令说明

返回日期日部分的值。

参数说明

date：必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值，格式为yyyy-mm-dd、yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回INT类型。

示例

静态数据示例

--返回1。
select dayofmonth('2014-09-01');
--返回NULL。
select dayofmonth('20140901');
--返回NULL。
select dayofmonth(null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, dayofmonth(date1) as date1_dayofmonth, datetime1, dayofmonth(datetime1) as datetime1_dayofmonth, timestamp1, dayofmonth(timestamp1) as timestamp1_dayofmonth, date3, dayofmonth(date3) as date3_dayofmonth from mf_date_fun_t;

EXTRACT

命令格式

int extract(<datepart> from date|datetime|timestamp <date>)

命令说明

提取日期date中指定单位datepart的部分。

参数说明

datepart：必填。支持YEAR、MONTH、DAY、HOUR或MINUTE等时间取值。

date：必填。DATE、DATETIME、TIMESTAMP或STRING类型日期值，格式为yyyy-mm-dd、yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回INT类型。

示例

静态数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select  extract(year from '2019-05-01 11:21:00') year
         ,extract(month from '2019-05-01 11:21:00') month
         ,extract(day from '2019-05-01 11:21:00') day
         ,extract(hour from '2019-05-01 11:21:00') hour
         ,extract(minute from '2019-05-01 11:21:00') minute;
--返回值如下。
+------+-------+------+------+--------+
| year | month | day  | hour | minute |
+------+-------+------+------+--------+
| 2019 | 5     | 1    | 11   | 21     |
+------+-------+------+------+--------+
--返回NULL。
select  extract(year from null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select timestamp1, extract(year from timestamp1) year, timestamp2, extract(month from timestamp2) month from mf_date_fun_t;

FROM_UTC_TIMESTAMP

命令格式

datetime|timestamp from_utc_timestamp({any primitive type}*, string <timezone>)

命令说明

将一个UTC时区的时间戳转换成一个指定时区的时间戳。

参数说明

{any primitive type}*：必填。时间戳，支持TIMESTAMP、DATETIME、TINYINT、SMALLINT、INT或BIGINT数据类型。如果该参数为TINYINT、SMALLINT、INT或BIGINT数据类型，则单位为毫秒。

timezone：必填。指定需要转换的目标时区。

返回值说明

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

示例

静态数据示例

--输入参数为毫秒（ms），返回2017-08-01 04:24:00.0。
select from_utc_timestamp(1501557840000, 'PST'); 
--返回1970-01-30 08:00:00.0。
select from_utc_timestamp('1970-01-30 16:00:00','PST'); 
--返回1970-01-29 16:00:00.0。
select from_utc_timestamp('1970-01-30','PST'); 
--开启2.0新类型。此命令需要与SQL语句一起提交。返回2011-12-25 17:00:00.123。
set odps.sql.type.system.odps2=true;
select from_utc_timestamp(timestamp '2011-12-25 09:00:00.123456', 'Asia/Shanghai');
--开启2.0新类型。此命令需要与SQL语句一起提交。返回2011-12-25 01:55:00.0。
set odps.sql.type.system.odps2=true;
select from_utc_timestamp(timestamp '2011-12-25 06:55:00', 'America/Toronto');
--返回NULL。
select from_utc_timestamp('1970-01-30',null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select datetime1, from_utc_timestamp(datetime1,'PST') pst, timestamp1, from_utc_timestamp(timestamp1,'Asia/Shanghai') asia from mf_date_fun_t;

HOUR

命令格式

int hour(datetime|timestamp|string <date>)

命令说明

返回日期小时部分的值。

参数说明

date：必填。DATETIME、TIMESTAMP或STRING类型日期值，格式为yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回INT类型。

示例

静态数据示例

--返回12。
select hour('2014-09-01 12:00:00');
--返回12。
select hour('12:00:00');
--返回NULL。
select hour('20140901120000');
--返回NULL。
select hour(null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select datetime1, hour(datetime1) as datetime1_hour, timestamp1, hour(timestamp1) as timestamp1_hour from mf_date_fun_t;

LAST_DAY

命令格式

string last_day(date|datetime|timestamp|string <date>)

命令说明

返回该日期所在月份的最后一天日期。

参数说明

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

返回值说明

返回STRING类型的日期值，格式为yyyy-mm-dd。

示例

静态数据示例

--返回2017-03-31。
select last_day('2017-03-04');
--返回2017-07-31。
select last_day('2017-07-04 11:40:00');
--返回NULL。
select last_day('20170304');

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, last_day(date1) as date1_lastday, datetime1, last_day(datetime1) as datetime1_lastday, timestamp1, last_day(timestamp1) as timestamp1_lastday, date3, last_day(date3) as date3_lastday from mf_date_fun_t;

MINUTE

命令格式

int minute(datetime|timestamp|string <date>)

命令说明

返回日期分钟部分的值。

参数说明

date：必填。DATETIME、TIMESTAMP或STRING类型日期值，格式为yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回INT类型。

示例

静态数据示例

--返回30。
select minute('2014-09-01 12:30:00'); 
--返回30。
select minute('12:30:00');
--返回NULL。
select minute('20140901120000');
--返回NULL。
select minute(null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select datetime1, minute(datetime1) as datetime1_minute, timestamp1, minute(timestamp1) as timestamp1_minute from mf_date_fun_t;

MONTH

命令格式

int month(datetime|timestamp|date|string <date>)

命令说明

返回一个日期的月份。

参数说明

date：必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值，格式为yyyy-mm-dd、yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回INT类型。

示例

静态数据示例

--返回9。
select month('2014-09-01');
--返回NULL。
select month('20140901');
--返回NULL。
select month(null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, month(date1) as date1_month, datetime1, month(datetime1) as datetime1_month, timestamp1, month(timestamp1) as timestamp1_month, date3, month(date3) as date3_month from mf_date_fun_t;

MONTHS_BETWEEN

命令格式

double months_between(datetime|timestamp|date|string <date1>, datetime|timestamp|date|string <date2>)

命令说明

返回日期date1和date2之间的月数。

参数说明

date1、date2：必填。DATETIME、TIMESTAMP、DATE或STRING类型，格式为yyyy-mm-dd、yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回DOUBLE类型。

示例

静态数据示例

--返回3.9495967741935485。
select months_between('1997-02-28 10:30:00', '1996-10-30');
--返回-3.9495967741935485。
select months_between('1996-10-30','1997-02-28 10:30:00' );
--返回-3.0。
select months_between('1996-09-30','1996-12-31');
--返回NULL。
select months_between('1996-09-30',null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select timestamp1, timestamp2, months_between(timestamp1, timestamp2) from mf_date_fun_t;

NEXT_DAY

命令格式

string next_day(timestamp|date|datetime|string <startdate>, string <week>)

命令说明

返回大于指定日期startdate并且与week相匹配的第一个日期，即下周几的具体日期。

参数说明

startdate：必填。TIMESTAMP、DATE、DATETIME或STRING类型日期值，格式为yyyy-mm-dd、yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

week：必填。STRING类型，一个星期前2个或3个字母，或者一个星期的全名。例如MO、TUE或FRIDAY。

返回值说明

返回STRING类型的日期值，格式为yyyy-mm-dd。

示例

静态数据示例

--返回2017-08-08。
select next_day('2017-08-01','TU');
--返回2017-08-08。
select next_day('2017-08-01 23:34:00','TU');
--返回NULL。
select next_day('20170801','TU');
--返回NULL。
select next_day('2017-08-01 23:34:00',null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, next_day(date1,'MON') as date1_next_day, datetime1, next_day(datetime1,'TUE') as datetime1_next_day, timestamp1, next_day(timestamp1,'WED') as timestamp1_next_day, date3, next_day(date3,'THU') as date3_next_day from mf_date_fun_t;

QUARTER

命令格式

int quarter (datetime|timestamp|date|string <date>)

命令说明

返回一个日期的季度，范围是1~4。

参数说明

date：必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值，格式为yyyy-mm-dd、yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回INT类型。

示例

静态数据示例

--返回4。
select quarter('1970-11-12 10:00:00');
--返回4。
select quarter('1970-11-12');
--返回NULL。
select quarter(null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, quarter(date1) as date1_quarter, datetime1, quarter(datetime1) as datetime1_quarter, timestamp1, quarter(timestamp1) as timestamp1_quarter, date3, quarter(date3) as date3_quarter from mf_date_fun_t;

SECOND

命令格式

int second(datetime|timestamp|string <date>)

命令说明

返回日期秒数部分的值。

参数说明

date：必填。DATETIME、TIMESTAMP或STRING类型日期值，格式为yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回INT类型。

示例

静态数据示例

--返回45。
select second('2014-09-01 12:30:45');
--返回45。
select second('12:30:45');
--返回NULL。
select second('20140901123045');
--返回NULL。
select second(null);

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
elect datetime1, second(datetime1) as datetime1_second, timestamp1, second(timestamp1) as timestamp1_second from mf_date_fun_t;

TO_MILLIS

命令格式

bigint to_millis(datetime|timestamp <date>);

命令说明

将给定日期date转换为以毫秒为单位的UNIX时间戳。

参数说明

date：必填。DATETIME或TIMESTAMP类型日期值，格式为yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回BIGINT类型。

示例

静态数据示例

--返回1617174900000。
select to_millis(datetime '2021-03-31 15:15:00');
--返回1617174900000。
set odps.sql.type.system.odps2=true;
select to_millis(timestamp '2021-03-31 15:15:00');

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select datetime1, to_millis(datetime1) as datetime1_to_millis, timestamp1, to_millis(timestamp1) as timestamp1_to_millis from mf_date_fun_t;

YEAR

命令格式

int year(datetime|timestamp|date|string <date>)

命令说明

返回日期date的年。

参数说明

date：必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值，格式为yyyy-mm-dd、yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回INT类型。

示例

静态数据示例

--返回1970。
select year('1970-01-01 12:30:00');
--返回1970。
select year('1970-01-01');
--返回70。
select year('70-01-01');
--返回NULL。
select year('1970/03/09'); 
--返回NULL。
select year(null); 

表数据示例

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, year(date1) as date1_year, datetime1, year(datetime1) as datetime1_year, timestamp1, year(timestamp1) as timestamp1_year, date3, year(date3) as date3_year from mf_date_fun_t;