Hive常用的日期函数

1.获取当前日期、时间、时间戳、时间戳对应日期

-- 代码 -- 
select current_date() -- 当前日期
    ,current_timestamp() -- 当前默认时间
    ,from_utc_timestamp(current_timestamp(),'GMT+8') -- 转为东八区时间
    ,unix_timestamp()  -- 时间戳
    ,from_unixtime(unix_timestamp()) -- 时间戳对应时间(东八区)
    ,to_utc_timestamp(from_unixtime(unix_timestamp()),'GMT') -- 当前时间戳转为时间(默认时区)
-- 结果 --
2023-02-01
2023-02-01 09:57:43 -- (时间差8h)
2023-02-01 17:57:43
1675245463
2023-02-01 17:57:43
2023-02-01 09:57:43

注意：

current_timestamp() 获取的时UTC默认时区。

给定一个时间戳可基于from_utc_timestamp/to_utc_timestamp进行转换。

2.将日期转换为时间戳

代码:select unix_timestamp('2023-01-20 10:25:30') as dt
结果:1674181530

3.将时间戳转换为日期

-- 代码 --
select 
    from_unixtime(1674181530) as dt1
    ,from_unixtime(1674181530, 'yyyy-MM-dd') as dt2
    ,from_unixtime(1674181530, 'yyyyMMdd') as dt3
    
-- 结果 -- 
2023-01-20 10:25:30 
2023-01-20
20230120

4.日期格式转换

-- 代码 --
select date_format('2023-01-20 10:25:30','yyyy-MM-dd') as dt1
    ,date_format('2023-01-20 10:25:30','yyyyMMdd') as dt2
-- 结果 -- 
2023-01-20
20230120

5.获取年、月、日、天、小时、分钟、秒、周数、星期几、季节函数

-- 代码 -- 
select year('2023-01-20 10:25:30') as year
    ,month('2023-01-20 10:25:30') as month
    ,day('2023-01-20 10:25:30') as day
    ,hour('2023-01-20 10:25:30') as hour
    ,minute('2023-01-20 10:25:30') as minute
    ,second('2023-01-20 10:25:30') as second
    ,weekofyear('2023-01-20 10:25:30') as weekofyear       
    -- ,dayofweek('2023-01-20 10:25:30') as dayofweek      
    ,date_format('2023-01-20 10:25:30' ,'u') as dayofweek2 
    ,ceil(month('2023-01-20 10:25:30')/3) as season

-- 结果 --
2023
1
20
10
25
30
3 -- 此处为3,而非4
5
1

注意：

   如果当前年的第一个周，天数超过3天，那就是当前年的第一周；

   如果当前年的第一个周，天数小于等于3天，那就是上一年的最后一周。

weekofyear('2023-01-01') 结果为52;

weekofyear('2023-01-02') 结果为1.

6.日期/月份的加减函数

-- 代码 --
select date_add('2023-01-20',1)  as dt_add1
    ,date_sub('2023-01-20',1)    as dt_sub1
    ,add_months('2023-01-20',1)  as dt_add_month1
    ,add_months('2023-01-20',-1) as dt_sub_month1
-- 结果 --
2023-01-21
2023-01-19
2023-02-20
2022-12-20

7.日期小时/分钟的加减函数

-- 代码 --
select from_unixtime(unix_timestamp('2023-01-20 10:25:30')- 1*3600) as sub_hour1 -- 减1小时
    ,from_unixtime(unix_timestamp('2023-01-20 10:25:30') + 1*3600)  as add_hour1 -- 加1小时
    ,from_unixtime(unix_timestamp('2023-01-20 10:25:30') - 10*60) as sub_minute10 --减10分钟
    ,from_unixtime(unix_timestamp('2023-01-20 10:25:30') + 10*60) as sub_minute10 --加10分钟
-- 结果 -- 
2023-01-20 09:25:30
2023-01-20 11:25:30
2023-01-20 10:15:30
2023-01-20 10:35:30

8.两个日期(月份)相减函数

-- 代码1 -- 跨月数据
select day_diff1,month_diff1,day_diff2,month_diff2,day_diff2/day_diff1 as month_diff
from (
    select datediff('2023-01-20', '2022-12-20') as day_diff1 -- 结束日期在前
        ,months_between('2023-01-20', '2022-12-20') as month_diff1  
        ,datediff('2023-01-20', '2022-12-21') as day_diff2 -- 结束日期在前
        ,months_between('2023-01-20', '2022-12-21') as month_diff2 
)
-- 结果 --
31
1.0
30
0.9677
0.9677

-- 代码2 -- 当月数据
select datediff('2023-01-20', '2023-01-10') as day_diff1 -- 结束日期在前
    ,months_between('2023-01-20', '2023-01-10') as month_diff1 
    ,10/31 as  c1
    ,datediff('2023-02-20', '2023-02-10')  as day_diff2 -- 结束日期在前
    ,months_between('2023-02-20', '2023-02-10') as month_diff2 
    ,10/28 as c2
    ,10/31 as c3
-- 结果 --
10
0.32258
0.32258
10
0.32258
0.35714
0.32258

注意：

months_between使用时：

  若是跨月日期，则分母按照跨月整月天数（31/30/28）计算;

  若是同月日期，则分母统一使用31天进行计算。

9.获取年初（年末）、月初（月末）、周一（周日）

-- 代码 -- 
select 
    trunc('2023-01-20', 'YY') as year_first -- 年初
    ,date_sub(add_months(trunc('2023-01-20', 'YY'),12),1) as year_end -- 年末
    ,trunc('2023-01-20','MM') as month_first -- 月初
    ,last_day('2023-01-20') as month_end -- 月末
    ,date_sub(next_day('2023-01-20','Mon'),7) as monday  -- 周一
    ,date_sub(next_day('2023-01-20','Mon'),1) as sunday  -- 周日

-- 结果 -- 
2023-01-01
2023-12-31
2023-01-01
2023-01-31
2023-01-16
2023-01-22

10.获取当前日期的下个星期几（并非下周几）的日期

-- 代码 -- 
select 
    next_day('2023-01-20', 'MO')  as next_mo
    ,next_day('2023-01-20', 'TU') as next_tu
    ,next_day('2023-01-20', 'WE') as next_we
    ,next_day('2023-01-20', 'TH') as next_th
    ,next_day('2023-01-20', 'FR') as next_fr
    ,next_day('2023-01-20', 'SA') as next_sa
    ,next_day('2023-01-20', 'SU') as next_su
-- 结果 -- 
2023-01-23
2023-01-24
2023-01-25
2023-01-26
2023-01-27
2023-01-21 -- 本周六
2023-01-22 -- 本周日