3

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
Martin Traverso
  • 4,731
  • 15
  • 24
Minnie
  • 31
  • 1
  • 3

1 Answers1

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)
Martin Traverso
  • 4,731
  • 15
  • 24