hive 时间戳的转换方法

由于频繁使用，没有一个博文是完整的，所以整理归纳了一下，遇到了sqlServer的不同语法就也写下来了；

 

 

1、时间戳转成日期

hive :   select distinct from_unixtime(1441565203,‘yyyy/MM/dd HH:mm:ss’) from test_date;

sqlSever:    CONVERT ( VARCHAR ( 10 ), DATEADD ( S, enter_log.click_time / 1000, '1970-01-01 08:00:00' ), 120 ) visit_date,

注意 ：/1000是因为入参的时间戳是毫秒级单位的入参；

2、日期转成时间戳

hive :  select distinct unix_timestamp(‘20111207 13:01:03’) from test_date; // 默认格式为“yyyy-MM-dd HH:mm:ss“

hive :  select distinct unix_timestamp(‘20111207 13:01:03’,‘yyyyMMdd HH:mm:ss’) from test_date;

3、yyyymmdd和yyyy-mm-dd日期之间的切换

方法1: from_unixtime+ unix_timestamp
–20181205转成2018-12-05
hive :  select from_unixtime(unix_timestamp(‘20181205’,‘yyyymmdd’),‘yyyy-mm-dd’) from dual;

–2018-12-05转成20181205
hive :  select from_unixtime(unix_timestamp(‘2018-12-05’,‘yyyy-mm-dd’),‘yyyymmdd’) from dual;

方法2: substr + concat
–20181205转成2018-12-05
hive :  select concat(substr(‘20181205’,1,4),’-’,substr(‘20181205’,5,2),’-’,substr(‘20181205’,7,2)) from dual;

–2018-12-05转成20181205
hive :  select concat(substr(‘2018-12-05’,1,4),substr(‘2018-12-05’,6,2),substr(‘2018-12-05’,9,2)) from dual;
注：

1.值得注意的是，时间戳有可能是毫秒级的，然后这时候直接使用from_unixtime(1441565203,‘yyyy/MM/dd HH:mm:ss’)的话就会得到很奇怪的日期了，这时候要这样from_unixtime(cast(151331629920/1000 as int))，同样的，时间转成毫秒级的时间戳也要乘以1000，如：unix_timestamp(‘2018-12-18 00:38:50’)*1000

2.如何区分时间戳是秒级还是毫秒级呢？一般来说，常见的时间戳是10位数的，13位数的时间戳就是毫秒级的

 

4、根据时间戳格式化所在的周 所在的月数（补充判空逻辑）

  hive :  转当天日期：if(visit_time IS NULL , 0, cast(from_unixtime(if(visit_time>9999999999, cast(visit_time / 1000 as bigint), visit_time), 'yyyyMMdd') as int)) visit_date,
  hive :   转当年周数：cast(concat(from_unixtime(if(visit_time>9999999999, cast(visit_time / 1000 as bigint), visit_time), 'yyyy'), weekofyear( from_unixtime(if(visit_time>9999999999, cast(visit_time / 1000 as bigint), visit_time), 'yyyy-MM-dd')) ) as bigint) visit_weekth,
  hive :   转当年月份：cast(from_unixtime(if(visit_time>9999999999, cast(visit_time / 1000 as bigint), visit_time), 'yyyyMM') as bigint) visit_month

5、计算两个日期直接间隔的天数、周数、月数

由于业务需要分析用户不同周数 月数的留存情况，而转换之后的日期间隔比如 周数最大一年为52周 201952 和202052 ，两个周数直接相减并不能得到正确的间隔周数或者月数

间隔月数

select floor(months_between('2018-07-01','2018-02-04')) from default.dual
返回值为: 4

注意: 
1.日期格式为: yyyy-mm-dd；
2.如果日期格式为: yyyymmdd，则需要使用转换为yyyy-mm-dd
转换函数为：from_unixtime(unix_timestamp(,'yyyymmdd'),'yyyy-mm-dd')；
3.floor是取整函数。

 

6、datediff：返回开始日期减去结束日期的天数

select datediff('2015-04-09','2015-04-01');
输出：8

•  

7、date_sub：返回日期前n天的日期

select date_sub('2015-04-09',4);
输出：2015-04-05

8、date_add：返回日期后n天的日期

select date_add('2015-04-09',4);

9、hive 计算某一个日期属于星期几，如2018-05-20 是星期日

SELECT IF(pmod(datediff('2018-05-20', '1920-01-01') - 3, 7)='0', 7, pmod(datediff('2018-05-20', '1920-01-01') - 3, 7)) 
输出：7

10、hive返回上个月第一天和最后一天

--上个月第一天
select trunc(add_months(CURRENT_TIMESTAMP,-1),'MM')


select concat(substr(add_months(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1),1,7),'-01'); 


--上个月最后一天
select date_sub(trunc(CURRENT_TIMESTAMP,'MM'),1);

 

晴天霹雳！！！

做好了整个数据，突然到了2020/12/1 选择日期到2021年的时候 突然发现跨年的周数

2020-12-31 所在的周是2020-53  而 2021-01-01 拼接之后 变成2021-53，仔细研究了一下weekOfyear函数的描述

 

为了解决跨年的周数问题 百度了一下找到一个方法，就贴过来了

select year(date_sub(next_day(current_date,'MO'),4))*100+weekofyear(current_date);

 

不定期完善