hive 日期转换

1 日期格式yyyymmdd转成yyyy-mm-dd
from_unixtime(unix_timestamp(t2.dt,'yyyyMMdd'),'yyyy-MM-dd')
2 在以前的基础上加8个小时
from_unixtime(unix_timestamp(t1.created_timestamp)+8*3600,'yyyy-MM-dd HH:mm:ss')

select t1.created_timestamp, 
from_unixtime(unix_timestamp(t1.created_timestamp)+8*3600,'yyyy-MM-dd HH:mm:ss')
from sog_l1screen.v1_test_dw_sp_content_track_txt_dt t1
limit 10;
OK
2017-06-22 06:54:03 2017-06-22 14:54:03
2017-06-22 06:54:03 2017-06-22 14:54:03
2017-06-22 06:54:03 2017-06-22 14:54:03
2017-06-22 06:54:04 2017-06-22 14:54:04

3 13位时间戳，字段类型为string，转化成日期格式
​
 select from_unixtime(cast(apptime/1000 as bigint),'yyyy-MM-dd'), count(1)
 from sog_l1screen.dw_l1_display_orc_dt
 where dt='20170821'
 group by from_unixtime(cast(apptime/1000 as bigint),'yyyy-MM-dd');

select hour(cast(apptime/1000 as bigint)) 
from sog_l1screen.dw_l1_display_orc_dt where dt='20171126'limit 10;

4 计算周几
pmod(datediff(${Format_dt}, '2012-01-01') , 7) （周一：1， 周日：0）