Presto hive 时间相关函数使用笔记

专注自己

近期工作中用到一些时间函数的计算，因为不够熟练，使用后觉得很有必要梳理下近期用到的时间转换函数，便于后期自己可以翻阅笔记。工作环境使用的BI涉及到presto与hive,较多使用的是presto，一般有时候开发写的是hive语句，我拿过来看的时候又需要转为presto的用法，所以会存在用presto和hive的语法对日期进行转换。

1.先了解presto常用的两个时间函数

1）date_parse(string, format) → timestamp      parse函数是把字符串转换成日期
2）date_format(timestamp, format) → varchar    format函数是把日期时间转换成字符串

2. presto 取当前时间（假如current_date 是8月14日）

select now();                           --精确到今天的时分秒,运行结果：August 14, 2021, 5:31 PM
select current_date ;                   --精确到今天的年月日,运行结果：August 14, 2021, 12:00 AM
select current_date - interval '1' day; --精确到昨天的年月日,运行结果：August 13, 2021, 12:00 AM

3.取当前时间年、月、日、周几、季度（假如current_date 是8月14日）

select year(current_date); --获得该字段的年份
--2021
select month(current_date); --获得该字段的年份
select day(current_date); --获得该字段的年份
select day(cast('2021-08-14 00:00:00' as timestamp)); --获得该字段的日期(day of month)
select day_of_week(date('2021-08-14')); --获得该日期是本周第几天(今天周六)
select quarter(cast('2021-08-14' as timestamp)) --获得季度 
--3

--以上函数对于date和timestamp类型字段都适用。

3.presto 时间格式转换

select date_parse('20210814','%Y%m%d')   --parse函数是把字符串转换成日期
-- August 14, 2021, 12:00 AM
select date_format(date_parse('20210814','%Y%m%d'),'%Y-%m-%d')  --先转为日期再转为字符串
-- 2021-08-14
select date_format(date_parse('20210814','%Y%m%d'),'%Y%m%d') --格式不一致,结果形式不一致
-- 20210814

注意：如下两者的区别是当前时间current time 已经是时间戳，不需要用date_prase

select date_format(date_parse('20210814','%Y%m%d'),'%Y-%m-%d %H:%i:%s')
 -- 2021-08-14 00:00:00 
select date_format(current_date,'%Y-%m-%d %H:%i:%s')  --将当前日期2021年8月14日转为时间格式
 -- 2021-08-14 00:00:00

4.时间计算

例：当前日期（今天是20210814）减去1个月。 注意时间计算需要先转为时间格式

select date_format(now() -interval '1' month,'%Y%m%d')
--20210714  
select date_format(date_parse('20210814','%Y%m%d') -interval '1' month,'%Y%m%d')
--20210714 
注意：上面例子时间now()是时间格式,所以可以直接加减
下面例子'20210814'是文本格式,所以需先用date_parse转化为标准日期形式再加减

5.时间的加减date_add

date_add(unit,value,timestamp)
函数date_add可对时间进行偏移，参数unit为单位，比如年、月、日，value为偏移量，比如1、-1，如果value为负数，则为往前偏移

例：计算今天(current_date是20210814) 减去13天是哪一天

SELECT CAST(DATE_ADD('day',-13,current_date)as varchar(20))
--2021-08-01
SELECT CAST(DATE_ADD('day',-day(current_date)+1,current_date)as varchar(20))--计算当月第一日
--2021-08-01  
注：其中select day(current_date)是当前日期14
select CAST(DATE_ADD('day',-day(current_date),current_date)as varchar(20)) --计算上个月末最后一日
--2021-7-31
select CAST((DATE_ADD('day',-day(current_date),current_date) - interval '1' month)as varchar(20))--计算上上个月末最后一日
--2021-6-30

6.计算两个时间的差值date_diff

date_diff(unit, timestamp1, timestamp2) 
函数date_diff，用来计算两个时间的差值，算法是timestamp2 - timestamp1，返回的是单位为unit的整数

例1：计算20210702 与今天20210802 之间相差月份数

select date_diff('month', date_parse('20210701','%Y%m%d'),now())