How to convert one time zone to another in Spark Dataframe

user_12354741 0
Viewed 580

I am reading from PostgreSQL into Spark Dataframe and have date column in PostgreSQL like below:

last_upd_date
---------------------
"2021-04-21 22:33:06.308639-05"

But in spark dataframe it's adding the hour interval.

eg: 2020-04-22 03:33:06.308639

Here it is adding 5 hours to the last_upd_date column.

But I want output as 2021-04-21 22:33:06.308639

Can anyone help me how to fix this spark dataframe.

apache-spark-sql apache-spark scala
1 Answers

You can create an udf that formats the timestamp with the required timezone:

import java.time.{Instant, ZoneId}

val formatTimestampWithTz = udf((i: Instant, zone: String)
    => i.atZone(ZoneId.of(zone))
        .format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSS")))

val df = Seq(("2021-04-21 22:33:06.308639-05")).toDF("dateString")
  .withColumn("date", to_timestamp('dateString, "yyyy-MM-dd HH:mm:ss.SSSSSSx"))
  .withColumn("date in Berlin", formatTimestampWithTz('date, lit("Europe/Berlin")))
  .withColumn("date in Anchorage", formatTimestampWithTz('date, lit("America/Anchorage")))
  .withColumn("date in GMT-5", formatTimestampWithTz('date, lit("-5")))

df.show(numRows = 10, truncate = 50, vertical = true)

Result:

-RECORD 0------------------------------------------
 dateString        | 2021-04-21 22:33:06.308639-05 
 date              | 2021-04-22 05:33:06.308639    
 date in Berlin    | 2021-04-22 05:33:06.308639    
 date in Anchorage | 2021-04-21 19:33:06.308639    
 date in GMT-5     | 2021-04-21 22:33:06.308639
user_21298010
Related