Presto: I have a column in the same table as the data which specifies the time zone I want to offset my dates to

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

1 answer

  • answered 2019-10-15 07:09 Piotr Findeisen

    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.