Pyspark parse datetime field with day and month names into timestamp

user_4462161 0
Viewed 715

I'm not even sure where to start. I want to parse a column that is currently a string into a timestamp. The records look like the following:

Thu, 28 Jan 2021 02:54:17 +0000

What is the best way to parse this as a timestamp? I wasn't even sure where to start since it's not a super common way to store dates

pyspark apache-spark-sql apache-spark timestamp date
1 Answers

You could probably start from the docs Datetime Patterns for Formatting and Parsing:

import pyspark.sql.functions as F

df = spark.createDataFrame([("Thu, 28 Jan 2021 02:54:17 +0000",)], ['timestamp'])

df.withColumn(
    "timestamp",
    F.to_timestamp("timestamp", "E, dd MMM yyyy HH:mm:ss Z")
).show()

#+-------------------+
#|          timestamp|
#+-------------------+
#|2021-01-28 02:54:17|
#+-------------------+

However, since Spark version 3.0, you can no longer use some symbols like E while parsing to timestamp:

Symbols of ‘E’, ‘F’, ‘q’ and ‘Q’ can only be used for datetime formatting, e.g. date_format. They are not allowed used for datetime parsing, e.g. to_timestamp.

You can either set the time parser to legacy:

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

Or use some string functions to remove the day part from string before using to_timestamp:

df.withColumn(
    "timestamp",
    F.to_timestamp(F.split("timestamp", ",")[1], " dd MMM yyyy HH:mm:ss Z")
).show()
user_13865510
Related