Prestosql/Amazon Athena: Time Zone Change

Question

I need to change a UTC timestamp to 'US/Eastern' timestamp without changing the date and time - essentially update only the timezone information and later convert that to a different timezone.

For example (what I need):

'2021-06-09 19:00:36.000000' UTC --> '2021-06-09 19:00:36.000000' US/Eastern

Then I need to convert that to 'America/New_York'.

'2021-06-09 19:00:36.000000' US/Eastern --> '2021-06-09 16:00:36.000000' America/Los Angeles

---

When I try the query below, it's not giving me the correct results, since it is converting from UTC to America/Los Angeles. When it should be US/Eastern to America/Los Angeles.

SELECT id
, date_utc 
, CAST(date_utc AT TIME ZONE 'America/Los Angeles') AS date_la
FROM call_records

Answer 1

I'm not sure if this will work for Athena, as it's based on a very old version of Presto/Trino.

In recent versions of Trino (formerly known as PrestoSQL), you can do this:

• Cast the timestamp with time zone to timestamp to remove the timezone part.
• Then, use with_timezone to reinterpret the resulting timestamp in US/Eastern.
• Finally, use AT TIME ZONE to change the time zone of the resulting timestamp with time zone while preserving the instant.

Take a look at the example below:

trino:tiny> WITH t(ts) AS (VALUES TIMESTAMP '2021-06-09 19:00:36.000000 UTC')
         -> SELECT with_timezone(cast(ts as timestamp(6)), 'US/Eastern') AT TIME ZONE 'America/Los_Angeles'
         -> FROM t;
                     _col0
------------------------------------------------
 2021-06-09 16:00:36.000000 America/Los_Angeles
(1 row)