sql(presto语法）--- 时间格式的处理

注意：这些语法都是presto语法（和hive、sql存在小偏差）

A.sql处理各种时间格式转换的问题：

1.将timestamp时间格式 转换成字符串的'yyyyMMdd'格式

  created_time 类型是timestamp，格式是：‘2019-01-22 10:36:07.161’ 

  dt 类型是字符串（string）,格式是：‘20190122’

select format_datetime(created_time,'yyyyMMdd') as dt from table

 

2.将timestamp时间格式 转换成字符串的'yyyy-MM-dd'格式

  created_time 类型是timestamp，格式是：‘2019-01-22 10:36:07.161’ 

  dt 类型是字符串（string）,格式是：‘2019-01-22’

select format_datetime(created_time,'yyyy-MM-dd') as dt from table

 

3.将时间是字符串的'yyyyMMdd'格式 转化为 timestamp格式 （和1作用相反）

  dt 类型是字符串（string）,格式是：‘20190122’

  created_time 类型是timestamp，格式是：‘2019-01-22 ’

select cast(concat(substring(dt, 1,4), '-', substring(dt, 5,2), '-', substring(dt,7,2)) as timestamp)) as created_time

select cast(date_format(date_parse(dt,'%Y%m%d'),'%Y-%m-%d')as timestamp) created_time

4.将字符串格式的时间'yyyy-MM-dd'转换成timestamp 格式

  dt 类型是字符串（string), 格式：‘2019-01-22’

  create_time类型是timestamp，格式：‘2019-01-22’

select cast(dt as timestamp) as create_time

 

5.将北京时间格式2019-01-22 08:08:08 to unix时间格式

select to_unixtime(try_cast(‘2019-01-22 08:08:08’ as timestamp)) as dt

Results: dt

       1548144488

将unix时间格式 to 北京时间格式

select from_unixtime(1548144488) as dt

Result: dt

      2019-01-22 08:08:08.000

综合：format_datetime 规范时间的具体格式

select format_datetime(from_unixtime(to_unixtime(try_cast(created_time as timestamp))),'yyyy-MM-dd') as dt from table

 

B.比较常用的时间函数：前提是timestamp类型，所以你的表示时间的字段类型必须得转换为timestamp

  如果是timestamp类型了，可以直接用接下来得函数

  如果不是timestamp类型，要先转换成timestamp，参考A中情况对号入座

1.date(dt) 返回日期表达式

  比如dt =‘2019-01-22 10:36:07.161’

  date(dt) 返回 ‘2019-01-22’

2.current_date 获取当前日期时间

  select current_date from table #返回：2019-12-11（timestamp类型）

 

3.获取两个时间差的天数：select date_diff('day',cast('2018-09-05' as timestamp),cast('2018-09-07' as timestamp)) as time_diff

  获取dt 到当前的时间天数之差:  select date_diff('day',dt,current_date) as day_diff

  date_diff('day' or 'hour' or 'second',dt1,dt2) 'day','hour','second'表示天数、小时数、秒数

 

4.dt1 = dt2+interval '1'day    观察dt2后一天的数据

   dt1 between dt2 and dt2+interval 'n' day  观察dt之后的n天内的数据

  date(cast(concat(substring(vsl.dt, 1,4), '-', substring(vsl.dt, 5,2), '-', substring(vsl.dt,7,2)) as timestamp)) between date(a.time) and date(a.time)+interval '3' day