Converting UTC Timestamp in AWS Athena to Timestmap in time zone europe/berlin

Q&A
, , ,
1

Hi,

I have a columen rwk_date which is a timestamp where a utc time ist stored. I want to create another column for the local time. The local time is in time zone Europe/Berlin.

My Approach was the following:

rwk_date AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Europe/Berlin’ AS rwk_date_local

But the problem is that I the result is a string with time zone at the end:

rwk_date rwk_date_local
2024-05-22 08:40:03.000 2024-05-22 10:40:03.000 Europe/Berlin
2024-05-21 14:05:33.000 2024-05-21 16:05:33.000 Europe/Berlin
2024-05-24 17:12:25.000 2024-05-24 19:12:25.000 Europe/Berlin
2024-05-22 08:37:49.000 2024-05-22 10:37:49.000 Europe/Berlin
2024-05-22 09:08:54.000 2024-05-22 11:08:54.000 Europe/Berlin

When I try to cast the date to a timestamp with:

cast(rwk_date AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Europe/Berlin’ AS timestamp) AS rwk_date_local

I get a timestamp but the value is not correct, it is again the utc timestamp.

rwk_date rwk_date_local
2024-05-21 14:05:33.000 2024-05-21 14:05:33.000
2024-05-22 08:37:49.000 2024-05-22 08:37:49.000
2024-05-22 08:40:03.000 2024-05-22 08:40:03.000
2024-05-22 09:08:54.000 2024-05-22 09:08:54.000
2024-05-24 17:12:25.000 2024-05-24 17:12:25.000

Can you help me to achive my goal? I want a timestmap column rwk_date_local with the local time. I’m working with a custom sql query in aws athena.

2

Hi @Markus

To convert your date from UTC to Europe/Berlin local time, please try using the following calculated field.

Example:

rwk_date_local - parseDate(formatDate({rwk_date},"yyyy-MM-dd HH:mm:ss",'Europe/Berlin'), "yyyy-MM-dd HH:mm:ss")

Please refer to the below community post this might be helpful for you.

3

Thanks for your reply, but as I mentioned, I’m searching for a solution in the query and not via calculated field.

4

Hi @Markus

Since the AT TIME ZONE function in Athena returns a string with the timezone suffix, you can use the following approach to get only the timestamp part by casting and cleaning up the string. This workaround removes the timezone label from the timestamp, providing you with the local time as a pure timestamp value.

Example:

CAST(REPLACE(CAST(rwk_date AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Berlin'  AS VARCHAR), ' Europe/Berlin', '') AS TIMESTAMP) AS rwk_date_local

Please refer to the below documentation this might be helpful for you.

5

Hi @Xclipse ,

thanks a lot for your support. The solution works perfect :+1:, but I’m still a bit consternated that there is such a workaround neccessary for this simple requirement. :sweat_smile:

Anyway, thanks for the help!

Markus