HIVE日期函数总结

最新推荐文章于 2023-01-13 17:18:35 发布

陈伦(colby)

最新推荐文章于 2023-01-13 17:18:35 发布

阅读量495

点赞数

本文链接：https://blog.csdn.net/colby_chenlun/article/details/100712094

版权

本文全面总结了HIVE中处理日期的常用函数，包括日期的转换、加减、截取等操作，帮助你更好地进行大数据分析中的日期处理工作。

摘要由CSDN通过智能技术生成

select
     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    -- 本周最后一天_周日
    ,next_day(day,'TU')                                                                as next_tuesday     -- 当前日期的下个周二
    ,trunc(day,'MM')                                                                   as month_first_day  -- 当月第一天
    ,last_day(day)                                                                     as month_last_day   -- 当月最后一天
    ,to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3 -2,2,0),'-01'))          as season_first_day -- 当季第一天
    ,last_day(to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3,2,0),'-01')))   as season_last_day  -- 当季最后一天
    ,trunc(day,'YY')                                                                   as year_first_day   -- 当年第一天
    ,last_day(add_months(trunc(day,'YY'),12))                                          as year_last_day    -- 当年最后一天
    ,weekofyear(day)                                                                   as weekofyear       -- 当年第几周
    ,second(day)                                                                       as second           -- 秒钟
    ,minute(day)                                                                       as minute           -- 分钟
    ,hour(day)