In Athena I have timezones and datetimes stored in the database, and I would like to use the stored timezone to convert the datetimes

I can get this query to work

SELECT (my_date_time) AT TIME ZONE 'America/Los_Angeles' AS converted_time
FROM table_one;

How can I replace the hard coded timezone with a value from a field in the table e.g.

SELECT (my_date_time) AT TIME ZONE my_timezone AS converted_time
FROM table_one;

I get this error when I try the second query

line 8:41: mismatched input 'my_timezone'. Expecting: 'ZONE'

Edit: Adding to Guru Strons' answer this is how I used at_timezone

WITH dataset (t, tz) AS (
   SELECT
     table_one.my_date_time,
     table_two.timezone
   FROM table_one
   LEFT JOIN table_two ON table_one.id = table_two.table_one_id
 )
SELECT t, tz, at_timezone(t, tz) FROM dataset

CodePudding user response：

Ahtena supports at_timezone function:

WITH dataset (t, tz) AS (
    VALUES (TIMESTAMP '2001-08-22 03:04:05.321 America/Los_Angeles', 'America/New_York')
)

select at_timezone(t, tz)
FROM dataset

Output: