AT_TIMEZONE not converting the timestamp

Get Help
1

The main goal of my query is to convert a UNIX timestamp to a UTC timestamp and then to the local timezone but it's not working. Here is my query:

WITH final AS (
SELECT
cv.validated_at,
g.locale,
from_unixtime(cv.validated_at) AS event_time_UTC,
at_timezone(from_unixtime(cv.validated_at), g.locale) AS event_time_local_formatted
FROM
checkin_validated cv
LEFT JOIN
gyms g ON g.id = cv.gym_id
)
SELECT * FROM final
LIMIT 100

But you can see in my results that event_time_UTC and event_time_local_formatted are the same timestamp and I am losing my mind trying to understand why

image
image982×506 53 KB

Any ideas?

2

I had the same use case, nothing worked for me but this. I'm working with Athena.

    from_unixtime(
        to_unixtime(event_hour) + 
        (timezone_hour(at_timezone(current_timestamp, time_zone)) * 3600)
    ) as local_timestamp,
3

:sob: :sob: :sob: :sob:

This is truly unfortunate. But happy to know I am not alone.

1 Like
4

My code is not working for daylight savings, so back on search

5

This seems to work better.

cast(
from_unixtime(
to_unixtime(cast(event_hour as timestamp)) +
(timezone_hour(at_timezone(cast(event_hour as timestamp), time_zone)) * 3600) +
(timezone_minute(at_timezone(cast(event_hour as timestamp), time_zone)) * 60)
) as timestamp
) as local_timestamp,