0

I have a table which has the following columns:

Store  timezone              date_entry        date_exit
ABC    America/Los Angeles   1/1/2019 16:17    1/4/2019 16:17 
DEF    Central Standard Time 2/1/2019 13:12    2/15/2019 09:45

All the date_entry and date_exit are in UTC and I want to convert it to the desired timezone as specified in the timezone column. This is just a sample, I have data for multiple timezones and hard coding it is very cumbersome and time consuming. Is there a way in Presto where I can input this string from timezone column to offset the time to the desired timezone?

Currently I am doing this:

date_entry AT TIME ZONE 'America/Los_Angeles' AS date_entry

With CASE WHEN statements, Is there a way to use the timezone column to do this without case when in Presto?

I have looked into these questions and tried the prospective solutions and it did not work for me:

1) Can you use a column for the timezone parameter of AT TIME ZONE in Presto / Athena? 2) Presto SQL : Changing time zones using time zone string coming as a result of a query is not working

Bruce Wayne
  • 471
  • 5
  • 18

1 Answers1

1

Presto 320 adds with_timezone (for timestamp values) at_timezone (for timestamp with time zone values) exactly for this purpose.

This is equivalent of (hypothetical) AT TIME ZONE x where x is not constant.

Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82