PySpark: Transform date string to date with unusual format

Thadeu Melo 0
Viewed 42

I need to transform a column with strings in the following format:

2022-07-08T14:45:04.086Z
2022-07-02T23:23:33.964Z

Using datetime this format works:

  datetime.strptime("2022-07-08T14:45:04.001Z","%Y-%m-%dT%H:%M:%S.%fZ")

However, I can´t figure out a way to use pyspark.sql.fuctions.to_date pattern. I tried something like this

 df.select(col("modified"),to_date(col("modified"),"yyyy-mm-dd'T'HH:MM:SS.SSSS'Z'").alias("modified2")).show()

Without success.

pyspark python
2 Answers

You need to use the to_timestamp function.

df = df.withColumn('modified2', F.to_timestamp('modified'))
df.show(truncate=False)
过过招0

Your pattern for month and minutes looks incorrect. It should be MM for months, and mm for minutes.

spark.conf.set('spark.sql.legacy.timeParserPolicy', 'LEGACY')

spark.sparkContext.parallelize([("2022-07-08T14:45:04.001Z",)]).toDF(['ts_str']). \
    withColumn('ts', func.to_timestamp('ts_str', "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")). \
    withColumn('dt', func.to_date('ts_str', "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")). \
    show(truncate=False)

# +------------------------+-----------------------+----------+
# |ts_str                  |ts                     |dt        |
# +------------------------+-----------------------+----------+
# |2022-07-08T14:45:04.001Z|2022-07-08 14:45:04.001|2022-07-08|
# +------------------------+-----------------------+----------+
samkart0
Related