Hive 中关于时间的函数

1 发现和描述函数

-- 显示所有函数
SHOW FUNCTIONS;
-- 函数简短介绍
--from_unixtime(unix_time, format) - returns unix_time in the specified format
DESCRIBE FUNCTION from_unixtime;
-- 详细文档
DESCRIBE FUNCTION EXTENDED from_unixtime;

2 from_unixtime 函数

-- 查询 from_unixtime 函数详细文档
DESCRIBE FUNCTION EXTENDED from_unixtime;

接受参数 unix_time, format

unix_time 为整型，值为 1970-1-1 到现在的秒数

format 格式化参数,可以是 yyyy MM dd HH mm ss 的随意组合，如：‘yyyy-MM-dd HH:mm:ss’， ‘yyyy-MM-dd’, ‘yyyyMMdd’，‘yyyyMMdd HH:mm:ss’, ‘HH:mm:ss’

SELECT from_unixtime(0, 'yyyyMMdd') FROM dim.app LIMIT 1;
>> 19700101

SELECT from_unixtime(0, 'yyyy-MMdd HH:mm') FROM dim.app LIMIT 1;
>>1970-0101 08:00

3 unix_timestamp 函数

接受参数 date, pattern(可选)

date 为日期 可以是 yyyy MM dd HH mm ss 的随意组合，但 pattern 需要指定格式，不然会返回 NULL

--不加参数返回当前时间戳
SELECT unix_timestamp() FROM dim.app LIMIT 1;
>>1641645097

SELECT unix_timestamp("2022-01-08", 'yyyy-MM-dd') FROM dim.app LIMIT 1;
>>1641571200

--返回 NULL
SELECT unix_timestamp("20220108", 'yyyyMM-dd') FROM dim.app LIMIT 1;
>>NULL

select UNIX_TIMESTAMP('2022-01-08 20:20:30','yyyy-MM-dd');
>>1641571200

select UNIX_TIMESTAMP('2022-01-08','yyyy-MM-dd');
>>1641571200

3 其它一些函数 WEEKOFYEAR DATEDIFF

--返回当前日期
SELECT current_timestamp() FROM dim.app LIMIT 1;
>>2022-01-08 20:33:32.236

--格式需为 'yyyy-MM-dd HH:mm:ss'
select TO_DATE('2000-01-01 10:20:30');
>>2000-01-01

--年
select YEAR('2000-01-01 10:20:30');
>>2000

--月
select MONTH('2000-01-01 10:20:30');
>>1

--日
SELECT DAY('2000-03-01 10:20:30');
>>1

SELECT HOUR('2000-03-01 10:20:30');
SELECT MINUTE('2000-03-01 10:20:30');
SELECT SECOND('2000-03-01 10:20:30');

--返回一年的第几周
SELECT WEEKOFYEAR('2022-01-08 10:20:30');

--相差天数
SELECT DATEDIFF('2022-01-08', '2022-01-05');
>>3

SELECT DATE_ADD('2022-01-05', 3);
>>2022-01-08

SELECT DATE_SUB('2000-03-01', 5);

4 应用

4.1 ‘yyyyMMdd’ 转为 ‘yyyy-MM-dd’

--法一
SELECT from_unixtime(unix_timestamp('20220105','yyyyMMdd'),'yyyy-MM-dd')
>>2022-01-05
--法二（不推荐）
select concat(substr('20220105',1,4),'-',substr('20220105',5,2),'-',substr('20220105',7,2));
>>2022-01-05

4.2 求某周的第一天最后一天

select
     day
    ,unix_timestamp(day) -- 时间戳
    ,date_add(day,1 - dayofweek(day))                                                  as week_first_day   -- 本周第一天_周日
    ,date_add(day,7 - dayofweek(day))                                                  as week_last_day    -- 本周最后一天_周六
    ,date_add(day,1 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as week_first_day   -- 本周第一天_周一
    ,date_add(day,7 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as week_last_day    -- 本周最后一天_周日
  -- dayofweek在hive2.2.0开始支持，低版本的hive原生未提供dayofweek函数(获取一个日期是星期几的方法)，
  -- 所以只有我们自己编写udf函数提供，udf就不说了，在这里给出了一个使用hive原生函数获取星期几的技巧。
   ,date_format(from_unixtime(unix_timestamp('20190808','yyyyMMdd'),'yyyy-MM-dd'),'u')   as dayofweek1 --本周第几天 u必须小写
   ,date_format('2019-08-08','u') --本周第几天 u必须小写
   ,7- datediff(next_day('2019-08-07',"Sunday"),'2019-08-07')  --另外一种方式实现本周几
	
    ,dayofmonth(day)                              --本月本月第几天
    ,next_day(day,'TU')                                                                as next_tuesday     -- 当前日期的下一个周二 有可能还是本周的
    ,next_day('2019-08-07','Mon')   as next_Monday      -- 当前日期的下一个星期一 有可能还是本周的
    ,next_day('2019-08-07','Tue')   as next_Tuesday     -- 当前日期的下一个星期二 有可能还是本周的
    ,next_day('2019-08-07','Wed')   as next_Wednesday   -- 当前日期的下一个星期三 有可能还是本周的
    ,next_day('2019-08-07','Thu')   as next_Thursday    -- 当前日期的下一个星期四 有可能还是本周的
    ,next_day('2019-08-07','Fri')   as next_Friday      -- 当前日期的下一个星期五 有可能还是本周的
    ,next_day('2019-08-07','Sat')   as next_Saturday    -- 当前日期的下一个星期六 有可能还是本周的
    ,next_day('2019-08-07','Sun')   as next_Sunday      -- 当前日期的下一个星期日 有可能还是本周的	
    ,to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3 -2,2,0),'-01'))          as season_first_day -- 当季第一天
    ,trunc(day,'MM')                                                                   as month_first_day  -- 当月第一天
    ,trunc(day,'YY')                                                                   as year_first_day   -- 当年第一天
    ,last_day(add_months(trunc(day,'YY'),12))                                          as year_last_day    -- 当年最后一天
    ,last_day(day)                                                                     as month_last_day   -- 当月最后一天
    ,last_day(to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3,2,0),'-01')))   as season_last_day  -- 当季最后一天
    ,weekofyear(day)                                                                   as weekofyear1       -- 当年第几周
    ,weekofyear('2019-08-07')  - weekofyear(trunc('2019-08-07','MM')) +1               as weekofmonth1   --当月第几周
    ,second(day)                                                                       as second           -- 秒钟
    ,minute(day)                                                                       as minute           -- 分钟
    ,hour(day)                                                                         as hour             -- 小时
    ,day(day)                                                                          as day              -- 日期
    ,month(day)                                                                        as month            -- 月份
    ,lpad(ceil(month(day)/3),2,0)                                                      as season           -- 季度
    ,year(day)                                                                         as year             -- 年份
from (
    select '2022-01-08 01:01:01' as day union all
    select '2022-01-02 02:03:04' as day union all
    select '2019-03-02 03:05:07' as day union all
    select '2019-04-02 04:07:10' as day union all
    select '2019-05-02 05:09:13' as day union all
    select '2019-06-02 06:11:16' as day union all
    select '2019-07-02 07:13:19' as day union all
    select '2019-08-02 08:15:22' as day union all
    select '2019-09-02 09:17:25' as day union all
    select '2019-10-02 10:19:28' as day union all
    select '2019-11-02 11:21:31' as day union all
    select '2019-12-02 12:23:34' as day
) t1

参考：

Hive Date Functions
HIVE 计算指定日期本周的第一天和最后一天