Hive常用日期函數(shù)有哪些

1.當(dāng)前日期和時(shí)間

select current_timestamp
-- 2020-12-05 19:16:29.284 

2.獲取當(dāng)前日期，當(dāng)前是 2020-12-05

SELECT current_date; 
## OR 
SELECT current_date(); 
-- 2020-12-05 

3.獲取unix系統(tǒng)下的時(shí)間戳

SELECT UNIX_TIMESTAMP();
-- 1524884881 

4.當(dāng)前是 2020-12-05

select substr(current_timestamp, 0, 10);
-- current_timestamp 

5.當(dāng)前是 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.兩個(gè)日期之間的天數(shù)差

select datediff("2017-10-22", "2017-10-12");
-- 10
select datediff("2017-10-22 10:10:10", "2017-10-12 23:10:10");
-- 10
select datediff("2017-10-22 01:10:10", "2017-10-12 23:10:10");
-- 10 

8.時(shí)間截取

select from_unixtime(cast(substr("1504684212155", 0,10) as int)) dt;
-- 2017-09-06 15:50:12 

9.時(shí)間戳轉(zhuǎn)日期

語(yǔ)法: to_date(string timestamp)

select to_date(from_unixtime(UNIX_TIMESTAMP()));
-- 2018-04-28
select FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd 10:30:00');
- 2018-04-28 10:30:00
select concat(date_sub(current_date,1),' 20:30:00');
-- 2018-04-27 20:30:00
-- hive version 1.2.0
select 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來(lái)轉(zhuǎn)

date_add
next_day 

11. 附加題

有一個(gè)活躍會(huì)員表，每天分區(qū)維度是會(huì)員id，可以用device_id來(lái)代替，問(wèn)怎么計(jì)算最近七天連續(xù)三天活躍會(huì)員數(shù)，其中表(dws.dws_member_start_day)結(jié)構(gòu)如下表（dt是分區(qū),日期格式y(tǒng)yyy-MM-dd，每個(gè)分區(qū)有唯一device_id）：

device_id             string                                                                   
dt                    string                

解法套路

1.首先思考可以用到的日期函數(shù)datediff, date_sub/date_add

2.連續(xù)日期，連續(xù)問(wèn)題都會(huì)用到一個(gè)排名函數(shù)，但是排名函數(shù)的值是數(shù)值，要與日期的連續(xù)性做到映射，才方便分組，比如可以把日期映射到連續(xù)數(shù)字，或者數(shù)字映射到連續(xù)日期，實(shí)現(xiàn)這兩個(gè)的操作就是通過(guò)前面的datedff 和 date_sub組合，原理就是日期與日期相減即可得到連續(xù)整數(shù)，整數(shù)隨便與某個(gè)日期做相減即可得到連續(xù)的日期,其中date_sub可以是反向排序得到連續(xù)日期。

3.通過(guò)連續(xù)的排序日期或者排序id相減，然后分組即可解決此類問(wèn)題

1.在原表基礎(chǔ)上增加一列排序序號(hào)

SELECT device_id,
       dt,
       row_number() over(PARTITION BY device_id
                         ORDER BY dt) ro
FROM dws.dws_member_start_day

2.將序號(hào)轉(zhuǎn)為連續(xù)日期，或者把日期轉(zhuǎn)為連續(xù)數(shù)字，后成為gid

-- 2.1 序號(hào)轉(zhuǎn)為連續(xù)日期
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 

-- 2.2 日期轉(zhuǎn)為連續(xù)序號(hào)
SELECT device_id,
    dt,
    (datediff(dt, '2020-07-21') - row_number() over(PARTITION BY device_id
        ORDER BY dt)) gid
FROM 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 ) tmp
GROUP BY device_id,
    gid
HAVING count(1) < 3