后面是罕用日期函数总结，前面是一道间断日期的sql题目及其解法套路。
1.以后日期和工夫
select current_timestamp-- 2020-12-05 19:16:29.284 2.获取以后日期，以后是 2020-12-05
SELECT current_date; ## OR SELECT current_date(); -- 2020-12-05 3.获取unix零碎下的工夫戳
SELECT UNIX_TIMESTAMP();-- 1524884881 4.以后是 2020-12-05
select substr(current_timestamp, 0, 10);-- current_timestamp 5.以后是 2020-12-05
select date_sub(current_date, 1);--2020-12-04 6.yyyy-MM-dd HH:MM:ss 截取日期
select to_date("2017-10-22 10:10:10");-- 2017-10-22 select date_format("2017-10-22" "yyyy-MM")-- 2017-10 7.两个日期之间的天数差
select datediff("2017-10-22", "2017-10-12");-- 10select datediff("2017-10-22 10:10:10", "2017-10-12 23:10:10");-- 10select datediff("2017-10-22 01:10:10", "2017-10-12 23:10:10");-- 10 8.工夫截取
select from_unixtime(cast(substr("1504684212155", 0,10) as int)) dt;-- 2017-09-06 15:50:12 9.工夫戳转日期
语法: to_date(string timestamp)
select to_date(from_unixtime(UNIX_TIMESTAMP()));-- 2018-04-28select FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd 10:30:00');-- 2018-04-28 10:30:00select concat(date_sub(current_date,1),' 20:30:00');-- 2018-04-27 20:30:00-- hive version 1.2.0select date_format(date_sub(current_date,1),'yyyy-MM-dd 20:30:00'); 10.日期减少
留神：原始日期格局只反对两种：yyyy-MM-dd yyyy-MM-dd HH:mm:ss
否则都须要date_format来转
date_addnext_day 11. 附加题
有一个沉闷会员表，每天分区维度是会员id，能够用device_id来代替，问怎么计算最近七天间断三天沉闷会员数，其中表(dws.dws_member_start_day)构造如下表（dt是分区,日期格局yyyy-MM-dd，每个分区有惟一device_id）：
device_id string dt string 解法套路
1.首先思考能够用到的日期函数datediff, date_sub/date_add
2.间断日期，间断问题都会用到一个排名函数，然而排名函数的值是数值，要与日期的连续性做到映射，才不便分组，比方能够把日期映射到间断数字，或者数字映射到间断日期，实现这两个的操作就是通过后面的datedff 和 date_sub组合，原理就是日期与日期相减即可失去间断整数，整数轻易与某个日期做相减即可失去间断的日期,其中date_sub能够是反向排序失去间断日期。
3.通过间断的排序日期或者排序id相减，而后分组即可解决此类问题
1.在原表根底上减少一列排序序号
SELECT device_id, dt, row_number() over(PARTITION BY device_id ORDER BY dt) roFROM dws.dws_member_start_day2.将序号转为间断日期，或者把日期转为间断数字，后成为gid
-- 2.1 序号转为间断日期SELECT device_id, dt, datediff(dt, date_add('2020-07-20', row_number() over(PARTITION BY device_id ORDER BY dt))) gidFROM dws.dws_member_start_day -- 2.2 日期转为间断序号SELECT device_id, dt, (datediff(dt, '2020-07-21') - row_number() over(PARTITION BY device_id ORDER BY dt)) gidFROM dws.dws_member_start_day 3.分组筛选
SELECT device_id,count(1)FROM (SELECT device_id, dt, datediff(dt, date_add('2020-07-20', row_number() over(PARTITION BY device_id ORDER BY dt))) gid FROM dws.dws_member_start_day WHERE datediff(dt, CURRENT_DATE) BETWEEN -7 AND 7 ) tmpGROUP BY device_id, gidHAVING count(1) < 3 吴邪，小三爷，混迹于后盾，大数据，人工智能畛域的小菜鸟。
更多请关注