Hive SQL常用日期转换

1、yyyyMMdd日期标准化

from_unixtime(unix_timestamp('${bizdate}', 'yyyyMMdd'),'yyyy-MM-dd')

2、取上一月

date_format(add_months(concat(substr(t1.month, 1, 4),'-',cast(substr(t1.month, 5, 2) as int),'-','01'),1),'yyyyMM')

3、yyyyMMdd日期转为季度

concat(substr('${bizdate}',1,4),'-Q',floor(substr('${bizdate}',5,2)/3.1)+1)

4、上周一

select date_sub(current_date(),pmod(datediff(cast(current_date() as string),'2000-01-03'),7)+7)

5、上周日

select date_sub(current_date(),pmod(datediff(cast(current_date() as string),'2000-01-03'),7)+1)

6、上月第一天

select  add_months(trunc(current_date(),'MM'),-1)

7、上月最后一天

 select  date_add(trunc(current_date(),'MM'),-1)

8、当前季度第一天

select to_date(concat(date_format(current_date(),'y'),'-',floor((cast(date_format(current_date(),'M') as int)+2)/3)*3-2,'-',date_format(trunc(current_date(),'MM'),'dd')))

-- 已测试
date_format(concat(substr('${bizdate}',1,4),
                      '-',floor((cast(substr('${bizdate}',5,2) as int)+2)/3)*3-2,
                      '-','01'),'yyyy-MM-dd')

9、当前季度最后一天

select to_date(concat(date_format(current_date(),'y'),'-',floor((cast(date_format(current_date(),'M') as int)+2)/3)*3,'-','31'))

10、去年第一天

select  add_months(trunc(current_date(),'YY'),-12)

11、去年最后一天

select date_add(trunc(current_date(),'YY'),-1)

12 、当前周第一天

select date_add(from_unixtime(unix_timestamp('${bizdate}', 'yyyyMMdd'),'yyyy-MM-dd'),1 - case when dayofweek(from_unixtime(unix_timestamp('${bizdate}', 'yyyyMMdd'),'yyyy-MM-dd')) = 1 then 7 else dayofweek(from_unixtime(unix_timestamp('${bizdate}', 'yyyyMMdd'),'yyyy-MM-dd')) - 1 end) as Mo_s

13、当前周最后一天

select date_add(from_unixtime(unix_timestamp('${bizdate}', 'yyyyMMdd'),'yyyy-MM-dd'),7 - case when dayofweek(from_unixtime(unix_timestamp('${bizdate}', 'yyyyMMdd'),'yyyy-MM-dd')) = 1 then 7 else dayofweek(from_unixtime(unix_timestamp('${bizdate}', 'yyyyMMdd'),'yyyy-MM-dd')

14、日期处理大杂烩

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)                                                                         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 '2018-01-02 01:01:01' as day union all
    select '2018-02-02 02:03:04' as day union all
    select '2018-03-02 03:05:07' as day union all
    select '2018-04-02 04:07:10' as day union all
    select '2018-05-02 05:09:13' as day union all
    select '2018-06-02 06:11:16' as day union all
    select '2018-07-02 07:13:19' as day union all
    select '2018-08-02 08:15:22' as day union all
    select '2018-09-02 09:17:25' as day union all
    select '2018-10-02 10:19:28' as day union all
    select '2018-11-02 11:21:31' as day union all
    select '2018-12-02 12:23:34' as day
) t1
;

15、营销周

select 
        concat(substr(year_mon,1,4),substr(year_mon,6,2)) as year_mon, -- 年月
        mkt_week_id_of_mth, -- 当月第几个营销周
        (
            case when substr(mkt_weekend_date,9,2)<=7 then concat(substr(mkt_weekend_date,1,8),'01')
                 else date_sub(mkt_weekend_date,cast(date_format(mkt_weekend_date,'u') as int))
            end
        ) as start_natural_weekend_date, -- 自然周一日期
        mkt_weekend_date -- 自然周末日期
    from
        ld_brc_corp_prd.s_dim_date
    where 
        ds = '${bizdate}'
        and long_date_id <= from_unixtime(unix_timestamp('${bizdate}', 'yyyyMMdd'),'yyyy-MM-dd')
    group by 
        year_mon,mkt_week_id_of_mth,mkt_weekend_date