Presto SQL 时间&日期函数

时间&日期函数

-- 2021-04-15 14:40:18.791 Asia/Shanghai
select now()

-- 2021-04-15
select current_date

-- 14:10:41.586 Asia/Shanghai
select current_time

-- 2021-04-15 14:13:03.720 Asia/Shanghai
select current_timestamp

-- 昨天的年月日
select current_date - interval '1' day;

-- Asia/Shanghai
select current_timezone()

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

-- 2021-01-08
select cast('2021-01-08' as date)

-- 2021-01-08 00:00:00.000
select cast('2021-01-08' as timestamp)

-- 2021-01-10 00:00:00.000
select date_parse('20210110','%Y%m%d')

-- 输出date类型日期：2021-01-08
select date(cast('2021-01-08 10:36:15' as timestamp))
select date(cast('2021-01-08' as timestamp))

-- 输出varchar类型日期：2021-01-10
select format_datetime(date_parse('20210110','%Y%m%d'),'yyyy-MM-dd')

-- 输出varchar类型日期：2021-01-08
select format_datetime(from_unixtime(to_unixtime(cast('2021-01-08 13:53:36' as timestamp))),'yyyy-MM-dd')
select format_datetime(from_unixtime(to_unixtime(cast('2021-01-08' as timestamp))),'yyyy-MM-dd')

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

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

-- 输出date类型日期：2021-04-13
select date_add('day',-2,current_date)
select date_add('week',-2,current_date)
select date_add('month',-2,current_date)
select date_add('quarter',-2,current_date)
select date_add('year',-2,current_date)

select date_add('millisecond',-2,date_parse('20210110','%Y%m%d'))
select date_add('second',-2,date_parse('20210110','%Y%m%d'))
select date_add('minute',-2,date_parse('20210110','%Y%m%d'))
select date_add('hour',-2,date_parse('20210110','%Y%m%d'))

-- date_diff(unit, timestamp1, timestamp2) → bigint
select date_diff('day','2021-04-10','2021-04-15')

select date_diff('day',date_parse('20210105','%Y%m%d'),date_parse('20210110','%Y%m%d'))

select date_diff('day',cast('2020-01-05' as date),cast('2020-01-10' as date))
select date_diff('month',cast('2020-01-05' as date),cast('2020-01-10' as date))

Presto SQL 特殊用法与进阶函数

1 try 函数 与 try_cast 函数

try(expression)

try函数会其中表达式的异常情况，并将异常值返回为 NULL，如果不使用try函数，则语句出现异常时将直接报错导致查询失败

您还可以搭配coalesce函数使用特定值替换 NULL 值，比如下列，将字段a转换为整数，转换失败则可转换为 0

coalesce(try(cast('a' as integer)), 0)

以上类型转换可以使用 try_cast 函数来实现，try_cast的作用与cast函数一致，都是对值进行类型转换，区别在于try_cast在类型转换错误时会返回 NULL，避免造成查询失败

coalesce(try_cast('a' as integer), 0)

2 时间/日期函数

使用current_date、current_time、current_timestamp、localtime与localtimestamp时不用加圆括号，Presto 也不支持加圆括号的写法，使用时请注意

2.1 字符串与时间的转换

可以直接在字符串格式的时间表达式前加关键字timestamp，比如timestamp '2020-01-01 00:00:00'，即可直接获得对应的时间

date_parse与date_format分别是字符串转时间以及时间转字符串，用法都是传入需要转化的字段以及对应的 format，如下分别是字符串$part_date转时间以及时间#event_time转字符串：

date_parse("$part_date", '%Y-%m-%d')

date_format("#event_time", '%Y-%m-%d %T')

以上函数的 format 格式使用的是 MySQL 的格式，如需使用 JAVA 格式，可使用函数format_datetime与parse_datetime

2.2 时间计算函数

函数date_add可对时间进行偏移，unit为单位，value为偏移量，如果value为负数，则为往前偏移

date_add(unit, value, timestamp)

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

date_diff(unit, timestamp1, timestamp2)

两个函数的unit的取值范围，可以参考以下表格

单位	描述
millisecond	毫秒
second	秒
minute	分钟
hour	小时
day	日
week	周
month	月
quarter	季度
year	年

3 开窗函数

Presto 支持开窗函数，窗口函数中有不少非常实用的函数，如 first_value 与 last_value 很适合计算一段时间内第一次或最后一次做某事的值。

比如计算每名用户首次产生购买商品行为时购买的物品：

SELECT user_id,first_purchase_product FROM
(SELECT user_id,first_value(product_name) over(partition by user_id order by time) AS first_purchase_product FROM log.purchase)
GROUP BY user_id,first_purchase_product

first_value与last_value需要搭配over子句使用，over子句中的partition by类似于group by，即按照所给字段分组，order by则会决定进行排序的字段

4 JSON 解析

在一些特殊场景下，我们建议通过字符串的方式记录复杂的数据结构，上传到后台。那么在之后的使用过程中，可以使用 JSON 解析函数转化或提取能在 SQL 中使用的数据

4.1 字符串转 JSON

json_parse可以将符合 JSON 格式的字符串转成 SQL 中的 JSON 类型数据，两函数等价：

json_parse('{"abc":[1, 2, 3]}')

4.2 JSON 转其他类型

转化为 JSON 的数据，可以通过CAST转化为其他 SQL 类型的数据，比如将刚刚转成 JSON 的字符串再次转成 MAP：

CAST(json_parse('{"abc":[1, 2, 3]}') AS MAP(varchar,array(integer)))

如果希望将字符串重新转成 JSON，可以使用json_format：

json_format(json_parse('{"abc":[1, 2, 3]}'))

4.3 直接提取 JSON 数据

在很多情况下，只需要提取 JSON 中的部分数据即可，此时可以使用json_extract_scalar进行提取，其通过 JSONPath 表达式进行提取，返回字符串：

json_extract_scalar(json, json_path)

并且json_extract_scalar可以直接对 JSON 的字符串进行提取，无需手动转化为 JSON 类型，比如以下，提取abc的第一个元素：

json_extract_scalar('{"abc":[1, 2, 3]}','$.abc[0]')