Jellyland

SQL的时间/日期无脑转换

最近在公司写数据需求的时候,由于各个表里存的日期和时间格式不尽相同,所以时常会遇到各种格式的日期转换的问题,一度搞得我非常烦躁。后来终于忍无可忍,仔细地去研究了一波,发现其实并没有想象中的那么麻烦,在此做个总结。

其实主要涉及的几种日期格式就是以下几种:

类型 示例
TIMESTAMP ‘2017-08-08 15:56:31.111’
STRING(‘yyyyMMdd’) ‘20170808’
DATE(‘yyyy-MM-dd’) ‘2017-08-08’
INT 20170808

其中主要会用到两个非常万能的函数,from_unixtime() 和 unix_timestamp()。

于是,在遇到时间格式转换的问题时,我们可以这样做:首先将原格式用UNIX_TIMESTAMP()转换为UNIX时间戳,再用unix_timestamp()转成所需的格式即可,这种方法个人感觉非常无脑但确实有效。

比如需要将‘2017-08-08’转换为‘20170808’,按照上面的无脑思路,直接from_unixtime(unix_timestamp('2017-08-08','yyyy-MM-dd'),'yyyyMMdd') 即可,反之也是一样的道理。

当然这其中存在很多捷径,比如TO_DATE()可以直接将TIMESTAMP转为DATE,当然要比以上说的这种无脑方法方便很多,但是个人觉得记忆起来很麻烦,而且这些函数各自也都具有一些局限性,只能将某些特定格式转换为另一些特定格式。

最后还应注意一点,也是我最近时常遇到的问题,就是在做表的联结的时候,以日期相等作为联结条件时,一定要确保等式两边的格式和类型都是一样的,'2017-08-08' = ‘20170808’这个等式并不成立。