由于频繁使用,没有一个博文是完整的,所以整理归纳了一下,遇到了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);
不定期完善