convert string with UTC offset to spark timestamp

user_1272230 0
Viewed 2481

How to store string 2018-03-21 08:15:00 +03:00 as a timestamptype, preserving the UTC offset, in spark?

tried below

from pyspark.sql.functions import *

df = spark.createDataFrame([("2018-03-21 08:15:00 +03:00",)], ["timestamp"])
newDf= df.withColumn("newtimestamp", to_timestamp(col('timestamp'), "yyyy-MM-dd HH:mm:ss XXX")
)

This prints newtimestamp column with value converted to UTC time i.e 2018-03-21 05:15:00

How I can store this string as timestamp column in dataframe preserving offset i.e store same string as timestamp or store like 2018-03-21 08:15:00 +3000

pyspark apache-spark-sql apache-spark datetime-format
1 Answers

You need to format the timestamp you obtain from the convertion to the desired pattern using date_format:

newDf = df.withColumn(
    "newtimestamp",
    to_timestamp(col('timestamp'), "yyyy-MM-dd HH:mm:ss XXX")
).withColumn(
    "newtimestamp_formatted",
    date_format("newtimestamp", "yyyy-MM-dd HH:mm:ss Z")
)

newDf.show(truncate=False)

#+--------------------------+-------------------+-------------------------+
#|timestamp                 |newtimestamp       |newtimestamp_formatted   |
#+--------------------------+-------------------+-------------------------+
#|2018-03-21 08:15:00 +03:00|2018-03-21 06:15:00|2018-03-21 06:15:00 +0100|
#+--------------------------+-------------------+-------------------------+
user_13865510
Related