hive&presto日期与字符串转换

由于presto小数据量比hive查询快的多，日常查询作业会经常在hive和presto中转换，但二者函数或部分语法不一样，尤其是日期，因此简单梳理了hive与presto最近一段时间日期时间转换

1、获取当前时间

-->hive

select from_unixtime(unix_timestamp());  --> 2021-01-06 22:53:16    --精确到今天的时分秒
select from_unixtime(unix_timestamp('2021-12-07 13:01:03'),'yyyy-MM-dd HH:mm:ss'); -->2021-12-07 13:01:03 --精确到今天的时分秒指定格式
select current_date; --> 2021-01-06   -- 今天的年月日
select unix_timestamp(); --> 1609944783  --获取当前时间戳

--> presto
select now();或者select current_timestamp --> 2021-01-06 22:49:08.899 Asia/Shanghai  --精确到今天的时分秒毫秒且带时区
select current_date; --> 2021-01-06  -- 今天的年月日
select current_date - interval '1' day; --> 2021-01-05  --昨天的年月日

2、字符串日期转时间戳

-->hive

select unix_timestamp('2021-01-08 10:36:15','yyyy-MM-dd HH:mm:ss')  --> 输出long类型timestamp：1610073375

--> presto

select to_unixtime(cast('2021-01-08 13:53:36' as timestamp))  --> 输出long类型timestamp：1610085216

3、时间戳转字符串日期

--> hive

select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')  -->输出varchar类型日期：2021-01-08 15:04:08

如果时间戳所在的字段是字符串，需要转换下类型，如下

select from_unixtime(CAST(CAST('1720627199000' AS BIGINT) / 1000 AS BIGINT),'yyyy-MM-dd HH:mm:ss')

--> presto

select format_datetime(from_unixtime(1610085216),'yyyy-MM-dd HH:mm:ss') -->输出varchar类型日期：2021-01-08 13:53:36

如果时间戳所在的字段是字符串，需要转换下类型，如下

select format_datetime(from_unixtime(CAST('1720627199000' AS BIGINT) / 1000), 'yyyy-MM-dd HH:mm:ss')

4、字符串日期格式转换

--> hive
select from_unixtime(unix_timestamp('2021-01-08 10:36:15','yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd') -->输出varchar类型日期：2021-01-08
select to_date('2021-01-08 10:36:15')  -->输出varchar类型日期：2021-01-08
select to_date(from_unixtime(unix_timestamp('20210110','yyyyMMdd'))); -->输出varchar类型日期：2021-01-10

--> presto
select date(cast('2021-01-08 10:36:15' as timestamp))  --> 输出date类型日期：2021-01-08
select format_datetime(from_unixtime(to_unixtime(cast('2021-01-08 13:53:36' as timestamp))),'yyyy-MM-dd') -->输出varchar类型日期：2021-01-08
select format_datetime(date_parse('20210110','%Y%m%d'),'yyyy-MM-dd') -->输出varchar类型日期：2021-01-10

5、date类型转字符类型串日期

--> presto

SELECT format_datetime(cast('2021-06-19' as date),'yyyy-MM-dd')  ->输出varchar类型日期：2021-06-19

6、日期加减

以date_add为例，hive的date_sub与date_add用法一样，而presto日期减有所区别date_diff(unit, timestamp1, timestamp2) ，输出的两个日期差的天数

--> hive

select date_add('2021-01-08',2);  -->输出varchar类型日期：2021-01-10

select date_add(current_timestamp,2); -->输出varchar类型日期：2021-01-10

--> presto

select date_add('day',-2,cast('2020-01-10' as date));  -->输出date类型日期：2021-01-08

select date_add('day',-2,current_date);   -->输出date类型日期：2021-01-06

select format_datetime(date_add('day',-1,cast('2021-06-19' as date)),'yyyy-MM-dd')

-->输出varchar类型日期：2021-06-18

7、日期截断

trunc日期截断，用于获取当天、当月及当年的第一天

--> hive

dd当天， MM所在月第一天，yyyy所在年第一天

SELECT trunc(CURRENT_DATE,'MM')   -->输出与输入类型一致的日期：2022-03-01

--> presto

day当天， month所在月第一天，year所在年第一天

SELECT date_trunc('month',CURRENT_DATE) -->输出与输入类型一致的日期：2022-03-01

参考：Date and Time Functions and Operators — Presto 0.271 Documentation

参考：presto 与 hive的时间转换对比 - 知乎