Search code examples
sqlamazon-athenaprestotrino

AWS Athena timestamp with offset


have json in s3 where the time values are given thus :

2023-03-26T02:07:34.930000+01:00

I need to convert this local time string into a UTC value.

Thought if I could at least cast to a timestamp value I could then add hours etc However tried converting into as timestamp with from_iso8601_timestamp but Athena won't let me use that fnct in a create view. "Unsupported give type" :(

Obviously I could parse & slice to get the "02" and add "1" (rolling over the day if it's greater than 24, or roll under if adding a negative).

Yet I am hoping that there is an easier 'out of the box' way of dealing with this scenario ?

Any guidance appreciated.


Solution

  • Based on provided timestamp format from_iso8601_timestamp combined with AT TIME ZONE can be used:

    select from_iso8601_timestamp('2023-03-26T02:07:34.930000+01:00') at time zone 'UTC';
    

    Output:

                _col0
    -----------------------------
     2023-03-26 01:07:34.930 UTC
    

    Or you can cast to timestamp:

    select cast(from_iso8601_timestamp('2023-03-26T02:07:34.930000+01:00') as timestamp);