Hive学习之一 ——时间日期相关

时间戳相关

1.获取设备中的当前时间戳

--int格式的时间戳。
select unix_timestamp();
--输出：1580988970

--string类型的时间戳。
select current_timestamp();
--输出：2020-09-17 14:16:31.658

2.时间戳格式化

--int时间戳转string类型时间戳。
select from_unixtime(1580988970,'yyyy-mm-dd hh:mm:ss');
--输出：2020-06-06 05:06:10

--毫秒时间戳转string类型时间戳
select from_unixtime(cast(1580988970000/1000 as bigint),'yyyy-mm-dd hh:mm:ss');
-->输出：2020-06-06 05:06:10
--ps：这里必须要转bigint，不然会报错

--string时间戳转int类型时间戳
select unix_timestamp('2020-06-06 05:06:10');
-->输出：1591400170

select unix_timestamp('20200907 13:01:03','yyyyMMdd HH:mm:ss')
--输出：1599483663

select unix_timestamp('2020-09-07 13:01:03','yyyyMMdd HH:mm:ss')
--输出：NULL

--ps:这里不知道为什么时间不一样，没有深究，机器有毒。

3.获取时间戳的年月日时分秒

--获取时间戳的年
select year(from_unixtime(1591400170));
select year('2020-09-10 09:10:20');
select year('2020-09-10');
--输出：2020

select year('20200910');
--输出NULL

--获取时间戳的月
select month(from_unixtime(1591400170));
select month('2020-06-10 09:10:20');
select nonth('2020-06-10');
--输出：6

--获取时间戳的日期
select day(from_unixtime(1591400170));
select day('2020-06-06 09:10:20');
select day('2020-06-6');
--输出：6

--获取时间戳的时
select hour(from_unixtime(1591400170));
select hour('2020-06-06 23:09:20');
--输出：23

--获取时间戳的分
select minute(from_unixtime(1591400170));
select minute('2020-06-06 23:36:20');
--输出：36

--获取时间戳的秒
select second(from_unixtime(1591400170));
select second('2020-06-06 23:36:10');
--输出：10

String转Date

可以参考以下链接 String To Date 大全

---

获取特殊日期

1.获取当月第一天

select trunc('2020-09-10','MM');
--输出：2020-09-01

2.获取当月最后一天

select last_day('2020-09-10');
--输出：2020-09-30
select last_day('2020-09-10 10:20');
--输出：2020-09-30
select last_day('2020-09-10 10:20:00');
--输出：2020-09-30

3.判断当天是本年第几周

select weekofyear('2020-09-17');
--输出：38

4.判断当天是本周第几天

 select pmod(datediff('2020-09-17','1920-01-01')-3,7);
--输出：4

日期函数

1.日期比较函数: datediff语法: datediff(string enddate,string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
例如：

select datediff('2020-09-20','2020-09-10');
--输出：10

select datediff('2020-09-20','2020-09-9');
--输出：11

select datediff('2020-09-20','20200909');
--输出：NULL

select datediff('2020-09-10','2020-09-20');
--输出：-10

2.日期增加函数: date_add语法: date_add(string startdate, intdays)
返回值: string
说明: 返回开始日期startdate增加days天后的日期
例如：

select date_add('2020-09-10',3);
--输出：2020-09-13

select date_add('2020-09-10',-3);
--输出：2020-09-07

select date_add('20200910',3);
--输出：NULL

3.日期减少函数: date_sub语法: date_sub (string startdate,int days)
返回值: string

说明: 返回开始日期startdate减少days天后的日期。
例如：

select date_sub('2020-09-10',3);
--输出：2020-09-07

select date_sub('2020-09-10',-3);
--输出：2020-09-13

select date_sub('20200910',3);
--输出：NULL

使用业务场景

1.对于工作日，需要看每周的周几在app的dau用户最多，以此确认每周周几更适合做一些运营活动

select
	--周几
	pmod(datediff(from_unixtime(unix_timestamp(cast(dt as string),'yyyymmdd'),'yyyy-mm-dd'),'1920-01-01')-3,7) as week_day
	--人数
	,count(distinct userid) as uv
	--几周
	,count(1) as cnt
from db_name.table_name
where dt>=20200803
	and dt<=20200913
group by
	pmod(datediff(from_unixtime(unix_timestamp(cast(dtas string),'yyyymmdd'),'yyyy-mm-dd'),'1920-01-01')-3,7)

2.需要看最后一次活跃日期离当前日期的差值的用户分布

select
	datediff(from_unixtime(unix_timestamp(cast(dt as string),'yyyymmdd'),'yyyy-mm-dd'),from_unixtime(unix_timestamp(cast(last_active_time as string),'yyyymmdd'),'yyyy-mm-dd')) as active_diff
	,count(distinct userid) as uv
from db_name.table_name
where dt=20200916
group by
	datediff(from_unixtime(unix_timestamp(cast(dt as string),'yyyymmdd'),'yyyy-mm-dd'),from_unixtime(unix_timestamp(cast(last_active_time as string),'yyyymmdd')))

持续更新