一.timestamp 类型 转换为其他类型
| 场景 | presto用法 | hive用法 |
| 转换成北京时区的string字符串，格式为y-m-d | date_format(created_at AT TIME ZONE 'Asia/Shanghai','%Y-%m-%d') 如果需要到时分秒，可在%Y-%m-%d'基础上加格式 | from_unixtime(unix_timestamp(from_utc_timestamp(`created_at`,'Asia/Shanghai')), 'yyyy-MM-dd') 如果需要到时分秒，格式为：yyyy-MM-dd HH:mm:ss |
| 转换为时间戳（10位） | to_unixtime(cast (‘2020-04-30 10:10:15’ as timestamp)) | unix_timestamp(cast (‘2020-04-30 10:10:15’ as timestamp)) |
二.时间戳(10位) 转换为其他
| 场景 | presto用法 | hive用法 |
| 转换为timestamp,带时区 | cast(DATE_FORMAT(from_unixtime(created) AT TIME ZONE 'Asia/Shanghai','%Y-%m-%d') as timestamp)
| |
| 转换为日期格式的string字符串 | DATE_FORMAT(from_unixtime(created) AT TIME ZONE 'Asia/Shanghai','%Y-%m-%d') | |
三.其他常见日期处理函数
--当前 日期（0时区）
select CURRENT_DATE
--0时区
select CURRENT_timestamp
--北京时间
select from_utc_timestamp(CURRENT_timestamp,'Asia/Shanghai')
date_format(created_at AT TIME ZONE 'Asia/Shanghai','%Y-%m-%d')
取上个月份 substr(add_months(CURRENT_DATE,-1),1,7)
取上个月末最后一天: date_sub(trunc(a.gl_date,'MM'),1)
presto 取上个月：
date_format( date_add('month',-1,created_at) ,'%Y-%m')
四.presto 的一些特性（和mysql对比）
操作方式 | presto/hive | mysql |
|---|---|---|
| 等于 | presto严格的类型，等号两边字段类型要一致。如果不一致，需要手工 cast（字段 as int/varchar）转成一致，时间类型要转成varchar并且做字符串截取才能比较， 例如substr(cast(shipped_at as varchar),1,10). between '2019-02-01' and '2019-03-01' | 无限制 |
| 列名 | 中文列名要用双引号 | 无限制 |
| 判断语句 | if(), case when 判断条件1 then 结果1 when 判断条件2 then 结果2 else 结果3 end 建议用第二种 | if() |
| 空值设置默认值 | coalesce(列名,0) presto和hive都可以使用 如果第一个值为null（空），则转为0 | nvl(列名,0) |
| decode操作 | nexr_decode （case when 简化版） 例子:nexr_decode(a,b,c,d) 如果a=b,则c，else 则d。简单的case when代替写法 | decode |
| 空值表达方式 | '',null 和0 presto中 这三个值是不同意思 | 不强制区别'',null 和0 |
| json解析 | presto：json_extract(json_parse(sku_info),'$.purchase_price_rmb') hive版本：get_json_object | json_extract |
| 时区转换 | date_format(created_at AT TIME ZONE 'Asia/Shanghai','%Y-%m-%d') | date_format(convert_tz(created_at, '+00:00', '+08:00'), '%y-%m-%d') |
| 时间差,小时差 | presto date_diff('day',created_at,updated_at) hive 版本 是datediff() | |
| date_diff | presto 执行是 0 ： select date_diff('day' , timestamp '2019-04-04 07:33:59.000',timestamp '2019-04-05 00:00:00.000') 解决方法： presto如果想和mysql 保持一致 select date_diff('day' , date(timestamp '2019-04-04 07:33:59.000'),date(timestamp '2019-04-05 00:00:00.000')) | mysql 执行是 1 ： select DATEDIFF(timestamp '2019-04-05 00:00:00.000',timestamp '2019-04-04 07:33:59.000') |