1、DATEADD
按照指定的单位和幅度修改日期值。
返回2005-03-01 00:00:00。加1天,结果超出当年2月份的最后一天,实际值为下个月的第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-02-28 00:00:00。2005年2月没有29日,日期截取至当月最后1天。
select dateadd(datetime '2005-01-29 00:00:00', 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');
2、DATE_ADD
按照指定的幅度增减天数,与date_sub的增减逻辑相反。
返回2005-03-01。加1天,结果超出当年2月份的最后1天,实际值为下个月的第1天。
select date_add(datetime '2005-02-28 00:00:00', 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);
可见,dateadd 与 date_add 类似
3、DATE_FORMAT
将日期值转换为指定格式的字符串
format可由如下日期字段组成,例如yyyy-MM-dd hh:mm:ss:SSS或yyyy-MM-dd hh:mi:ss:SSS:
YYYY或yyyy代表年份。
MM代表月份。
mm代表分钟。
dd代表天。
HH代表24小时制时。
hh代表12小时制时。
mi代表分钟。
ss代表秒。
SSS代表毫秒。
打开Hive模式。与SQL语句一起提交。
set odps.sql.hive.compatible=true;
假设当前时间为2022-04-24 15:49。返回2022-49-24 03:49:01.902。
select date_format(from_utc_timestamp(current_timestamp(), 'UTC'),'yyyy-mm-dd hh:mm:ss.SSS');
–返回2022-04-24。
select date_format('2022-04-24','yyyy-MM-dd');
4、DATE_SUB
按照指定的幅度增减天数,与date_add的增减逻辑相反。
开启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);
返回NULL。
select date_sub('2005-03-01 00:00:00', null);
5、DATEDIFF
计算两个日期的差值并按照指定的单位表示。
假设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');
假设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');
6、DATEPART
提取日期中符合指定时间单位的字段值。
返回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);
7、DATETRUNC
提取日期按照指定时间单位截取后的值。
返回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);
8、FROM_UNIXTIME
将数字型的UNIX值转换为日期值。
返回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);
9、GETDATE
获取当前系统时间。
datetime getdate()
10、ISDATE
判断一个日期字符串能否根据指定的格式串转换为一个日期值。
返回True。
select isdate('2021-10-11','yyyy-mm-dd');
返回false。
set odps.sql.type.system.odps2=false;
select isdate(1678952314,'yyyy-mm-dd');
11、LASTDAY
获取日期所在月的最后一天。
返回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);
12、TO_DATE
将指定格式的字符串转换为日期值。
返回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');
返回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);
13、TO_CHAR
将日期按照指定格式转换为字符串。
返回阿里金融2010-12*03
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');
返回NULL。
select to_char(datetime'2010-12-03 00:00:00', null);
14、UNIX_TIMESTAMP
将日期转换为整型的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);
15、WEEKDAY
返回日期值是当前周的第几天。
返回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);
16、WEEKOFYEAR
返回日期值位于当年的第几周。
返回1。虽然20141229属于2014年,但是这一周的大多数日期是在2015年,因此返回结果为1,表示是2015年的第一周。
select weekofyear(to_date("20141229", "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);
17、ADD_MONTHS
计算日期值增加指定月数后的日期。
返回2017-05-14。
select add_months('2017-02-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);
18、CURRENT_TIMESTAMP
返回当前TIMESTAMP类型的时间戳。
返回’2017-08-03 11:50:30.661’。
set odps.sql.type.system.odps2=true;
select current_timestamp();
19、DAY
返回日期值的天。
返回1。
select day('2014-09-01');
–返回NULL。
select day('20140901');
–返回NULL。
select day(null);
20、DAYOFMONTH
返回日部分的值。
返回1。
select dayofmonth('2014-09-01');
返回NULL。
select dayofmonth('20140901');
返回NULL。
select dayofmonth(null);
21、EXTRACT
获取日期TIMESTAMP中指定单位的部分。
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);
22、FROM_UTC_TIMESTAMP
将一个UTC时区的时间戳转换为一个指定时区的时间戳。
--输入参数为毫秒(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);
23、HOUR
返回日期小时部分的值。
--返回12。
select hour('2014-09-01 12:00:00');
--返回12。
select hour('12:00:00');
--返回NULL。
select hour('20140901120000');
--返回NULL。
select hour(null);
24、LAST_DAY
返回日期值所在月份的最后一天日期。
--返回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');
25、MINUTE
返回日期分钟部分的值。
--返回30。
select minute('2014-09-01 12:30:00');
--返回30。
select minute('12:30:00');
--返回NULL。
select minute('20140901120000');
--返回NULL。
select minute(null);
26、MONTH
返回日期值所属月份。
--返回9。
select month('2014-09-01');
--返回NULL。
select month('20140901');
--返回NULL。
select month(null);
27、MONTHS_BETWEEN
返回指定日期值间的月数。
--返回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);
28、NEXT_DAY
返回大于日期值且与指定周相匹配的第一个日期。
--返回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);
29、QUARTER
返回日期值所属季度。
--返回4。
select quarter('1970-11-12 10:00:00');
--返回4。
select quarter('1970-11-12');
--返回NULL。
select quarter(null);
30、SECOND
返回日期秒数部分的值。
--返回45。
select second('2014-09-01 12:30:45');
--返回45。
select second('12:30:45');
--返回NULL。
select second('20140901123045');
--返回NULL。
select second(null);
31、TO_MILLIS
将指定日期转换为以毫秒为单位的UNIX时间戳。
--返回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');
32、YEAR
返回日期值的年。
--返回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);
33、注意事项
升级到MaxCompute 2.0后,产品扩展了部分函数。函数涉及新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY),在使用扩展函数时,需要执行如下语句开启新数据类型开关:
Session级别:如果使用新数据类型,您需要在SQL语句前加上语句set odps.sql.type.system.odps2=true;并与SQL语句一起提交执行。
Project级别:Project Owner可根据需要对Project进行设置,等待10~15分钟后才会生效。命令如下。
setproject odps.sql.type.system.odps2=true;
参考链接:https://help.aliyun.com/document_detail/48974.html