Hive 日期时间相关函数总结

Hive 日期时间相关函数

版本环境：hive-2.1.1

一、日期获取

current_date() 获取当前日期

select current_date(); --返回类型'yyyy-mm-dd'

current_timestamp() 获取当前日期时间

select current_timestamp();  --返回格式'yyyy-mm-dd hh:mi:ss'

unix_timestamp() 取得当前时间戳、指定日期时间戳

select unix_timestamp();
select unix_timestamp('20220324', 'yyyyMMdd'); --返回20220324的时间戳

from_unixtime(时间戳,日期格式) 时间戳转换

select from_unixtime(1648103561,'yyyy-MM-dd HH:dd:ss'); -- 后面可以填想要的日期格式不局限于举的例子
select from_unixtime(1648103561,'yyyy-MM-dd');

to_nuix_timestamp(日期时间,日期时间格式) 日期时间转时间戳

select to_unix_timestamp('2022-03-24 14:24:41','yyyy-MM-dd HH:dd:ss');
select to_unix_timestamp('2022-03-24 14:24:41','yyyy-MM-dd'); -- 会按照传入的格式去截取，传入的格式不对结果会异常

取得当前时间 from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:dd:ss')

select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:dd:ss');

二、日期计算相关函数

date_add(日期时间,nd) 日期加减（按日），传入日期格式需要为yyyy-MM-dd可搭配其他函数一起食用

select date_add('2022-03-24',-1); -- 返回2022-03-23
select date_add('2022-03-24',5);  -- 返回2022-03-29
select date_add(from_unixtime(unix_timestamp('20220324','yyyyMMdd'),'yyyy-MM-dd'),-1); -- 返回2022-03-23
select date_add(from_unixtime(unix_timestamp('20220324','yyyyMMdd'),'yyyy-MM-dd'),5); -- 返回2022-03-29

add_months(日期时间,nm) 日期加减(按月)，传入日期格式需要为yyyy-MM-dd可搭配其他函数一起食用

select add_months('2022-03-28',1); -- 返回 2022-04-28
select add_months('2022-02-28',1); -- 返回 2022-03-31 返回的不是03-28这个需要注意
select add_months('2022-04-30',-1); -- 返回 2022-03-31 这种情况也需要注意

next_day(参数1，参数2) 返回下一个星期的某一天，具体看例子

参数1：参数1为yyyy-MM-dd格式的日期,如果为yyyy-MM-dd HH:mm:ss的格式会截取掉时间，建议搭配其他日期函数转换成yyyy-MM-dd
参数2：参数2为周一到周日的英文字符串，可以是简写Monday、Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday

-- 3-24 为周四
select next_day('2022-03-24','Mon'); -- 返回下一个周一的日期 2022-03-28
select next_day('2022-03-24','Sun'); -- 返回下一个周日（本周日） 2022-03-27
select next_day('2022-03-23','Fri'); -- 返回 2022-03-25
select next_day('2022-03-24','Fri'); -- 返回 2022-03-25

last_day(日期时间) 返回当月最后一天，日期格式需要为yyyy-MM-dd

select last_day('2022-03-24'); -- 返回 2022-03-31

date_sub(startdate,days) 返回startdate减去days天数的日期。返回VARCHAR类型的yyyy-MM-dd日期格式。若有参数为null或解析错误，返回null

select date_sub('2022-03-24',24); -- 返回 2022-02-28
select date_sub('2022-03-24',-8); -- 返回 2022-04-01

trunc(date, fmt) 为指定元素而截去的日期值 我测试的时候在hive里fmt参数必需要大写，Oracle里大小写都行,而且参数类型也没Oracle丰富，参考文章写的是hive，不排除版本的原因

date 日期时间
fmt 指定的元素截取格式

select trunc('2022-03-24','MM'); -- 返回date当月第一天 2022-03-01
select trunc('2022-03-24','YY'); -- 返回date当年第一天 2022-01-01
select trunc('2022-03-24','YYYY'); -- 返回date当年第一天 2022-01-01
select trunc('2022-03-24','YEAR'); -- 返回date当年第一天 2022-01-01

datediff(date1,date2) 取得两个日期之间差值（差值为天数）date1-date2，日期格式需要为yyyy-MM-dd

select datediff('2022-03-24','2022-03-20'); -- 返回值 4
select datediff('2022-03-24','2022-03-30'); -- 返回值 -6

三、格式转换

to_date()字符串转date类型（字符串必须为：yyyy-MM-dd格式）

select to_date('2022-03-24 15:36:36'); -- 返回2022-03-24

date_format()日期、时间戳、字符串类型格式化输出标准时间格式

select date_format('2022-03-24','yyyy年MM月dd日 HH:mm:ss'); -- 可以格式化成想要的效果，第一个参数需要满足满足yyyy-MM-dd格式

四、常用日期处理（总之不断的套娃就能得到你想要的结果，处理方法可能不止文中的一种）

下文的例子输入日期格式和输出日期格式都为yyyyMMdd，根据你的需要调整

昨天

select date_format(date_sub(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),1),'yyyyMMdd'); -- 返回 20220323
select date_format(date_add(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),-1),'yyyyMMdd'); -- 返回 20220323

本月初

select date_format(trunc(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),'MM'),'yyyyMMdd'); -- 返回 20220301

本月底

select date_format(last_day(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd')),'yyyyMMdd'); -- 返回 20220331

上月初

select date_format(trunc(add_months(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),-1),'MM'),'yyyyMMdd'); -- 返回 20220201

上月底

select date_format(last_day(add_months(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),-1)),'yyyyMMdd'); -- 返回 20220228

去年同期

select date_format(add_months(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),-12),'yyyyMMdd');

本周一

select date_format(next_day(date_add(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),-7),'Mon'),'yyyyMMdd'); -- 返回 20220321

本周日

select date_format(date_add(next_day(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),'Mon'),-1),'yyyyMMdd'); -- 返回 20220327

上周一

select date_format(next_day(date_add(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),-14),'Mon'),'yyyyMMdd'); -- 返回 20220314

上周日

select date_format(date_add(next_day(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),'Mon'),-8),'yyyyMMdd'); -- 返回 20220320

本季度第一天

select date_format(to_date(concat(
                 substring('20220324', 1, 4),
                 CASE floor(cast(substring('20220324', 5, 2) AS double) / 3.1) + 1
                     WHEN 1 THEN "-01-01"
                     WHEN 2 THEN "-04-01"
                     WHEN 3 THEN "-07-01"
                     WHEN 4 THEN "-10-01" END
             )), 'yyyyMMdd'); -- 返回 20220101

去年同季度第一天

select date_format(add_months(to_date(concat(
                 substring('20220324', 1, 4),
                 CASE floor(cast(substring('20220324', 5, 2) AS double) / 3.1) + 1
                     WHEN 1 THEN "-01-01"
                     WHEN 2 THEN "-04-01"
                     WHEN 3 THEN "-07-01"
                     WHEN 4 THEN "-10-01" END
             )), -12), 'yyyyMMdd'); -- 返回 20210101

本年初

select date_format(trunc(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),'YY'),'yyyyMMdd'); -- 返回 20220101

本年底

select date_format(date_add(add_months(trunc(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),'YY'),12),-1),'yyyyMMdd'); -- 返回 20221231

去年初

select date_format(add_months(trunc(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),'YY'),-12),'yyyyMMdd'); -- 返回 20210101

去年底

select date_format(date_add(trunc(from_unixtime(unix_timestamp('20220324', 'yyyyMMdd'),'yyyy-MM-dd'),'YY'),-1),'yyyyMMdd'); -- 返回 20211231

五、总结

先记录这些以后遇到了在补吧，这些函数在工作中应该够用了，多套娃吧。