如何在Spark SQL中格式化日期? [英] How to format date in Spark SQL?
问题描述
我需要将给定的日期格式: 2019-10-22 00:00:00
转换为以下格式: 2019-10-22T00:00:00.000Z
I need to transform this given date format: 2019-10-22 00:00:00
to this one: 2019-10-22T00:00:00.000Z
我知道这可以通过以下方式在某些数据库中完成:
I know this could be done in some DB via:
在AWS Redshift中,您可以使用以下方法实现此目标:
In AWS Redshift, you can achieve this using the following:
TO_DATE('{RUN_DATE_YYYY/MM/DD}', 'YYYY/MM/DD') || 'T00:00:00.000Z' AS VERSION_TIME
但是我的平台是Spark SQL,因此以上两种方法都不适合我,我能得到的最好的方法是:
But my platform is Spark SQL, so neither above two work for me, the best I could get is using this:
concat(d2.VERSION_TIME, 'T00:00:00.000Z') as VERSION_TIME
有点古怪,但仍然不完全正确,我得到了以下日期格式: 2019-10-25 00:00:00T00:00:00.000Z
,但是该字符串中间的 00:00:00
这部分是多余的,我不能将其留在此处.
which is a bit hacky, but still not completely correct, with this, I got this date format: 2019-10-25 00:00:00T00:00:00.000Z
,
but this part 00:00:00
in the middle of the string is redundant and I cannot leave it there.
任何有见识的人将不胜感激!
Anyone has any insight here would be greatly appreciated!
推荐答案
这是我自然的想法.
spark.sql("""SELECT date_format(to_timestamp("2019-10-22 00:00:00", "yyyy-MM-dd HH:mm:ss"), "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") as date""").show(false)
结果是:
+------------------------+
|date |
+------------------------+
|2019-10-22T00:00:00.000Z|
+------------------------+
这篇关于如何在Spark SQL中格式化日期?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!