【解决】spark-sql在unix_timestamp上的深坑

【问题发现】

如题，今天在使用hive和spark两个引擎执行SQL时，发现二者不一样的一个地方。如果你并不知道这个bug，那这个bug可以轻松让你废掉大半天：

简述一下要做的事：事情很简单，我有一个形如'yyyymmdd'格式的日期，如20220531，我现在想将其后移一天，即输出20220601.按照hive的函数限制，我们要先将20220531这种'yyyymmdd'格式的日期转化为'yyyy-mm-dd'格式的2022-05-31，然后再date_add一下，最后再转回'yyyymmdd'格式，即可大功告成

直接上hive-sql代码

hive> 
SELECT
from_unixtime(unix_timestamp(date_add(from_unixtime(unix_timestamp('20220531','yyyymmdd'),'yyyy-mm-dd'), 1),'yyyy-MM-dd'),'yyyyMMdd')
FROM db.xxx_tbl
hive> '20220601'

如上所示，sql的可读性确实差，但逻辑很简单，先yyyymmdd->yyyy-mm-dd，然后date_add(, 1)，最后yyyy-mm-dd->yyyymmdd。括号拆开一看便知

然后我将含这段代码的sql在spark上运行，得出的结果竟然是这样的：

spark-sql>
SELECT
from_unixtime(unix_timestamp(date_add(from_unixtime(unix_timestamp('20220531','yyyymmdd'),'yyyy-mm-dd'), 1),'yyyy-MM-dd'),'yyyyMMdd')
FROM db.xxx_tbl
spark-sql>'20220001'

预期结果为20220601，得到的确实20220001，压根不是个合法日期。那么到底是哪出了问题？

---

【解决】

由于想起来java在日期字符串格式化时，使用大写的“MM”表示的月份，而在sql里默认是小写"mm"。所以怀疑spark-sql作为java为底层撰写的引擎，是否这里有差别。故将上述三步骤的第三步（yyyy-mm-dd->yyyymmdd）的mm都改成了MM。即如下code

spark-sql>
SELECT 
   from_unixtime(unix_timestamp(date_add(from_unixtime(unix_timestamp(dt,'yyyymmdd'),'yyyy-mm-dd'), 1),'yyyy-MM-dd'),'yyyyMMdd')as dt_pro
FROM db.xxx_tbl
spark-sql> '20220601'

这样简单一改，果然结果就对了。后来再查了一些资料后发现，spark-sql的unix_timestamp函数，都是要求'yyyyMMdd'这种大写格式的月份的。所以，在hive-SQL转移到spark-sql的过程中，莫不如将所有日期格式的'mm'都写成'MM'吧

• 参考：Scala: Spark SQL to_date(unix_timestamp) returning NULL - Stack Overflowhttps://stackoverflow.com/questions/40433065/scala-spark-sql-to-dateunix-timestamp-returning-null