apache-spark - Pyspark - from_unixtime 没有显示正确的日期时间

Question

我想将包含纪元时间的时间戳列转换为日期时间(人类可读)。 from_unixtime 没有给我正确的日期和时间。请帮忙。

df = spark.createDataFrame([('1535934855077532656',), ('1535934855077532656',),('1535935539886503614',)], ['timestamp',])

df.show()

+-------------------+
|          timestamp|
+-------------------+
|1535934855077532656|
|1535934855077532656|
|1535935539886503614|
+-------------------+

df.withColumn('datetime',from_unixtime(df.timestamp,"yyyy-MM-dd HH:mm:ss:SSS")).select(['timestamp','datetime']).show(15,False)

+-------------------+----------------------------+
|timestamp          |datetime                    |
+-------------------+----------------------------+
|1535934855077532656|153853867-12-24 10:24:31:872|
|1535934855077532656|153853867-12-24 10:24:31:872|
|1535935539886503614|153875568-09-17 05:33:49:872|
+-------------------+----------------------------+

Answer 1

来自_unix_time

Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the given format.

你的数据显然不是用秒表示的。也许纳秒？

 from pyspark.sql.functions import col, from_unixtime


df.withColumn(
    'datetime',
   from_unixtime(df.timestamp / 1000 ** 3,"yyyy-MM-dd HH:mm:ss:SSS")
).show(truncate=False)

# +-------------------+-----------------------+
# |timestamp          |datetime               |
# +-------------------+-----------------------+
# |1535934855077532656|2018-09-03 02:34:15:000|
# |1535934855077532656|2018-09-03 02:34:15:000|
# |1535935539886503614|2018-09-03 02:45:39:000|
# +-------------------+-----------------------+