[1144]Hive常用日期格式转换

select unix_timestamp()

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

select from_unixtime(cast(create_time/1000 as bigint),'yyyyMMdd') as dt

SELECT CURRENT_DATE;   --2017-06-15
select current_date();   -- 2021-10-22

SELECT current_timestamp; --返回时分秒
--2018-06-18 10:37:53.278
SELECT from_unixtime(unix_timestamp());
--2017-06-15 19:55:04

select from_unixtime(unix_timestamp('20211022','yyyyMMdd'),"yyyy-MM-dd");
2021-10-22

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
类似sql 中的date

select substr('2021-10-22 17:34:56',1,10)
2021-10-22

select date_format('2021-10-22 17:34:56','yyyy-MM-dd')
2021-10-22

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') 

select date_add('2016-08-16',10)

select date_sub('2016-08-16',10)

前一日/昨日
select date_sub(current_date(),1);
2021-10-21

最近一个月/30天
select date_sub(current_date(),30);
2021-09-22

select concat(date_format(date_sub(current_date(),1),'yyyy-MM-dd'),' ','12');
2021-10-21 12

select trunc('2016-08-16','MM') --2016-08-01

select date_format(to_date(trunc(current_date(),'MM')),"yyyy-MM-dd");
2021-10-01

select trunc('2016-08-16','YEAR') --2016-01-01