Hive常用日期函数

当前日期和时间：
select current_timestamp();
– 2018-04-28 11:46:03.136
获取当前日期
select current_date;
select current_date();
select substr(current_timestamp, 0, 10);
– 2018-04-28
获取unix系统下的时间戳
select unix_timestamp();
– 1524884881
获取昨天日期(获取前几天日期)
select date_sub(current_date, 1);
将时间转化伟日期
select to_date(“2017-10-22 10:10:10”);
–2018-10-22
获取两个日期之间的天数差
select datediff(“2017-10-22”, “2017-10-12”);
– 10

–20180905转成2018-09-05
select from_unixtime(unix_timestamp(‘20180905’,‘yyyymmdd’),‘yyyy-mm-dd’)
from dw.ceshi_data
–结果如下：
2018-09-05

–2018-09-05转成20180905
select from_unixtime(unix_timestamp(‘2018-09-05’,‘yyyy-mm-dd’),‘yyyymmdd’)
from dw.ceshi_data
–结果如下：
20180905

固定日期转换成时间戳
select unix_timestamp(‘2016-08-16’,‘yyyy-MM-dd’) --1471276800
select unix_timestamp(‘20160816’,‘yyyyMMdd’) --1471276800
select unix_timestamp(‘2016-08-16T10:02:41Z’, “yyyy-MM-dd’T’HH:mm:ss’Z’”) --1471312961
16/Mar/2017:12:25:01 +0800 转成正常格式（yyyy-MM-dd hh:mm:ss）
select from_unixtime(to_unix_timestamp(‘16/Mar/2017:12:25:01 +0800’, ‘dd/MMM/yyy:HH:mm:ss Z’))
时间戳转换程固定日期
select from_unixtime(1471276800,‘yyyy-MM-dd’) --2016-08-16
select from_unixtime(1471276800,‘yyyyMMdd’) --20160816
select from_unixtime(1471312961) – 2016-08-16 10:02:41
select from_unixtime( unix_timestamp(‘20160816’,‘yyyyMMdd’),‘yyyy-MM-dd’) --2016-08-16
select date_format(‘2016-08-16’,‘yyyyMMdd’) --20160816
返回日期时间字段中的日期部分
select to_date(‘2016-08-16 10:03:01’) --2016-08-16
取当前时间
select from_unixtime(unix_timestamp(),‘yyyy-MM-dd HH:mm:ss’)
select from_unixtime(unix_timestamp(),‘yyyy-MM-dd’)
返回日期中的年
select year(‘2016-08-16 10:03:01’) --2016
返回日期中的月
select month(‘2016-08-16 10:03:01’) --8
返回日期中的日
select day(‘2016-08-16 10:03:01’) --16
返回日期中的时
select hour(‘2016-08-16 10:03:01’) --10
返回日期中的分
select minute(‘2016-08-16 10:03:01’) --3
返回日期中的秒
select second(‘2016-08-16 10:03:01’) --1
返回日期在当前的周数
select weekofyear(‘2016-08-16 10:03:01’) --33
返回结束日期减去开始日期的天数
select datediff(‘2016-08-16’,‘2016-08-11’)
返回开始日期startdate增加days天后的日期
select date_add(‘2016-08-16’,10)
返回开始日期startdate减少days天后的日期
select date_sub(‘2016-08-16’,10)
返回当天三种方式
SELECT CURRENT_DATE;
–2017-06-15
SELECT CURRENT_TIMESTAMP;–返回时分秒
–2017-06-15 19:54:44
SELECT from_unixtime(unix_timestamp());
–2017-06-15 19:55:04
返回当前时间戳
Select current_timestamp–2018-06-18 10:37:53.278
返回当月的第一天
select trunc(‘2016-08-16’,‘MM’) --2016-08-01
返回当年的第一天
select trunc(‘2016-08-16’,‘YEAR’) --2016-01-01

select trunc(current_date,‘YEAR’)

获取当年的第一月份

select substring(trunc(current_date,‘YEAR’),1,7)

获取上个月的月份：
select substr(add_months(current_timestamp,-1),1,7) --string

获取去年的第一天：
select add_months(trunc(current_date,‘YEAR’),-12);

获取去年的第一月份：
select substring(add_months(trunc(current_date,‘YEAR’),-12),1,7);

获取今年的第一月份：
select substring(add_months(trunc(current_date,‘YEAR’),0),1,7);

获取今年的最后一月份：
select substring(add_months(trunc(current_date,‘YEAR’),11),1,7);
获取去年当前月份：
select substring(add_months(current_date,-12),1,7);

获取去年的今天：
select add_months(current_date,-12);

timestamp 转 date 再转 timestamp

select from_unixtime(unix_timestamp(substring(t1.settle_date,1,10),‘yyyy-MM-dd’),‘yyyy-MM-dd HH:mm:ss’)

获取31天前的月份
select substring(date_sub(current_date, 31),1,7)

where month_id>=substring(date_sub(current_date, 31),1,7)

SELECT
pos,
SUBSTR( add_months (
FROM_UNIXTIME( unix_timestamp( SUBSTR( start_date, 1, 4 ), ‘yyyy’ ))
, pos ), 1, 7 ) AS ym
FROM
( SELECT current_date AS start_date ) tmp
lateral VIEW posexplode ( split ( space( 11 ), ‘’ ) ) t AS pos, val

SELECT
pos,
date_add( start_date, pos ) dd
FROM
( SELECT trunc(current_date,‘YEAR’) AS start_date, last_day(add_months(trunc(current_date,‘YY’),11)) AS end_date ) temp
lateral VIEW
posexplode ( split ( space( datediff( end_date, start_date ) ), ‘’ ) ) t
AS pos, val

select

 day     

  ,(day,1) -- 时间

,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,1)                              --本月本月第几天

,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             -- 年份

f
from (

select '2019-01-02 01:01:01' as day union all

select '2019-02-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
;
;