from_utc_timestamp not taking daylight saving time into account

Question

I need to convert timestamp in UTC to MST or EST time, but its not taking daylight saving into account. Also is it better to use MST or EST or should we use "America/Phoenix' or "America/New York".

Please help.

Thanks, Naveed

Answer 1

Use America/New_York for Eastren and America/Phoenix for Mountain timezones, Spark from_utc_timestamp function will automatically gets daylight timing covered for us.

Example:

#set timezone for the session    
spark.conf.set('spark.sql.session.timeZone', 'America/New_York')

#daylight saving time i.e -4:00 hrs from utc
spark.sql("""select current_timestamp as current_ts,from_utc_timestamp(current_timestamp,"America/New_York") utc_to_est""").show(10,False)
#+-----------------------+-----------------------+
#|current_ts             |utc_to_est             |
#+-----------------------+-----------------------+
#|2020-05-10 19:04:28.369|2020-05-10 15:04:28.369|
#+-----------------------+-----------------------+

#after daylight saving time that will be -5:00 hrs from utc.
spark.sql("""select from_utc_timestamp("2020-12-10 15:04:28.369","America/New_York") utc_to_est""").show(10,False)
#+-----------------------+
#|utc_to_est             |
#+-----------------------+
#|2020-12-10 10:04:28.369|
#+-----------------------+