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.
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);