大数据-玩转数据-MaxCompute SQL中的日期函数

最新推荐文章于 2024-04-23 09:20:41 发布

人猿宇宙

最新推荐文章于 2024-04-23 09:20:41 发布

阅读量2.2k

点赞数 1

分类专栏：大数据-玩转数据-阿里系文章标签： sql 大数据 odps

本文链接：https://blog.csdn.net/s_unbo/article/details/125365295

版权

大数据-玩转数据-阿里系专栏收录该内容

19 篇文章 13 订阅

订阅专栏

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

人猿宇宙

关注

1
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
大数据-玩转数据-MaxCompute SQL中的日期函数

假设start=2018-06-04193323.234，end=2018-06-04193323.250，含毫秒的日期不属于标准DATETIME式样，不能直接隐式转换，此处需进行显示转换。假设当前时间为2020-11-17163144，返回2020-11-16163144。将STRING类型常量显式转换为DATETIME类型，返回2005-02-28000000。假设当前时间为2020-11-17163144，返回2020-11-16。...
复制链接

扫一扫