cast函数_各种SQL中关于日期函数的那些事（建议收藏）

hive、presto、spark、impala、mysql常用日期时间函数吐血整理

使用的SQL多了不知道大家有没这样的困惑，SQL的语法在大多数方面是一致的，如SELECT,JOIN,GROUP BY等，但是在一些函数或某些特定功能处理上还是有很大差异的，而这些差异经常给大家带来困惑，尤其是一个新手从一种SQL转到另一种SQL的时候，总是抓耳挠腮，不知所措。今天就把大家常用的SQL语言做一个总结，来看看他们在日期时间处理上的差异。

前置说明：本文所用的日期时间均指：‘2020-07-20 10:58:59’这种格式，时间戳指：‘1595932031’这种格式。

一：时间戳转为日期

hive:select create_time,from_unixtime(create_time,'yyyy-MM-dd HH:mm:ss') from table1;  --这是标准的写法，如果不加'yyyy-MM-dd HH:mm:ss'同样可以返回到秒的结果，如果只需要格式化到小时、分钟等的话只给出到对应位置的格式化参数即可。presto: select create_time,from_unixtime(create_time),format_datetime(from_unixtime(create_time),'yyyy-MM-dd HH:mm:ss') from tables1;--from_unixtime不需要使用格式化参数来指定格式化的位数而且create_time的类型必须是数值型，如果不是需要先使用cast转为数值型才可，或者会报错，默认返回到毫秒经度。如果需要指定返回的精度配合format_datetime使用即可。spark:select create_time,from_unixtime(create_time,'yyyy-MM-dd HH:mm:ss') from table1; --由于spark底层使用的hive的执行解析计划，所以这里与hive的使用基本一致。impala:select create_time,from_unixtime(cast(create_time as bigint)+28800,'yyyy-MM-dd HH:mm:ss') from table1; --两个需要注意的地方，create_time不支持string类型，只能是数值型；这里加上28800(8个小时)主要是解决impala时区的问题，因为impala默认的不是中国时区，需要加上28800才能与正常的中国时区保持一致。mysql:select create_time,from_unixtime(create_time,'%Y-%m-%d %H:%i:%s) from table1;--这里需要注意的地方create_time必须是数值类型的；如果不加格式化参数的话默认是返回到秒的，需要使用格式参数的话加上对应的格式化参数即可。

以上的执行结果为：假设create_time为1522128932,转换后的结果为：'2018-03-27 13:35:32'

二：日期转为时间戳

hive:select unix_timestamp(create_time,'yyyy-MM-dd HH:mm:ss') from table1;--需要注意的地方：如果create_time是标准的到秒级的时间可以不指定格式化参数，如果不是标准的到秒级的日期必须根据create_time到哪一位后面对应到格式化话哪一位，否则会返回空值或者是不正确的结果。presto:select cast(to_unixtime( cast ( create_time as timestamp)) as bigint) from table1;--需要注意的地方，首先presto这里的转换使用起来比较麻烦，需要to_unixtime和timestamp结合起来使用才行。这里的create_time不用指定格式化的参数，会根据具体的值来解析。spark:select unix_timestamp(create_time,'yyyy-MM-dd HH:mm:ss') from table1;--与hive的使用保持一致。impala:select unix_timestamp(create_time,'yyyy-MM-dd HH:mm:ss')+28800 from table1;--需要注意的地方转换后需要加上28800才能与中国时区保持一致，create_time如果是标准的日期时间格式的话可以不指定格式化参数，否则必须要指定格式化参数。mysql:select UNIX_TIMESTAMP(created_time) from table1;--这里不需要指定格式化参数，或者会报错，需要特别注意

以上执行结果：假设create_time为'2018-03-27 13:35:32',转换后的结果为：1522128932

三：计算两个时间相差的天数

hive:selecct datediff(date1,date2) from table1;--计算两个日期之间的天数差值,是拿date1的日期“减去”date2的日期，即使date1和date2精确到的粒度不一致，如date1到天,date2到分钟结果仍然只是天粒度的差值。presto:select date_diff('day',cast(date1 as date),cast(date2 as date)) from table1;--这里需要三个参数，第一个参数指定计算的是“天”差值、“小时”差值等，另外需要把date1和date2转为date类型，否则SQL会报错。spark:selecct datediff(date1,date2) from table1;--使用hive保持一致impala:select datediff(date1,date2) from table1;--这里的使用基本上与hive保持一致mysql:selecct datediff(date1,date2) from table1;--基本与hive的用法一致

说明：有了以上两步日期和时间戳之间的互转，这里求两个日期的时间差值就相对来说比较简单了，如果不是标准的日期时间格式先转为日期时间格式即可。

四：计算某个日期的前N天或者后N天

hive:select date_add/date_sub(date1,N) from table1;--date_add和date_sub分别是向后推N天和向前推N天，另外这里增加或减少后日期只精确到天，即使date1是精确到秒粒度的计算结果最终仍然是到天粒度。presto:select date_add('day', N, cast(date1 as date) ) from table1;--这里同样需要三个参数后推日期的粒度，后推多少天,基准日期。另外需要注意preto这里没有date_sub函数，需要使用的话可以把第二个参数改为负值即可。spark:select date_add/date_sub(date1,N) from table1;--使用上与hive保持一致。impala:select adddate/days_add(date1,N) from table1;--在这个功能实现上adddate和days_add均可使用，需要注意date1要么精确到天(2020-07-01)要么精确到秒(2020-07-01 12:12:11)其他格式会返回空值。另外即使date1只精确到返回结果仍然是到秒的。mysql:select date_add/date_sub(date1,INTERVAL N DAY) from  table;--需要指定后推/前移的天数，如果date1只精确到天则结果也是精确到天，如果date1精确粒度到天后面的级别则会返回秒级别的粒度。

说明：大部分SQL都支持date_add/date_sub，其实使用一个即可，把相对应的N值取为负值即可。

五：获取当前日期时间

hive:select substr(current_timestamp(),1,19)/from_unixtime(unix_timestamp(),"yyyy-MM-dd HH:mm:ss") from table1;--两种方式均可，建议使用第一种更为简洁，返回的是标准的秒级粒度的日期时间。presto:select current_date/current_time from table1;--current_date返回的是天级粒度的日期时间(2020-01-02)这种，current_time  返回的是当前时间对应的小时、分钟和秒(12：12：11)这种。spark:select substr(current_timestamp(),1,19)/from_unixtime(unix_timestamp(),"yyyy-MM-dd HH:mm:ss") from table1;--使用上和hive保持一致impala:select mow() /current_timestamp() from table1;--比较简单，两个函数返回的结果相同，但是注意返回的是到毫秒的日期时间格式，如果需要到天粒度的话可以截取处理。mysql:select sysdate()/now() from table1;--两个函数返回的结果一致，都是到秒粒度的日期时间。

以hive为基准，从以上例子可以看出spark的语法基本完全兼hive;presto与其他几个相比使用起来稍显麻烦主要是由于其支持多种数据源，其上要做统一的封装；impala时区的问题需要注意，否则会带来数据上的困扰和不一致性。

备注：以上列出了大家工作中常用的一些SQL在日期处理上的一些差别，可能存在部分不严谨的地方，欢迎大家指出。另外在一些功能上也不限于以上提供的方式，大家如果有更好更简洁的方式也欢迎提出。

END