How to adjust timestamp timezone offset for YYYY-MM-DD''T''HH:mm:ss.SSSZ format timestamp in presto?

user_19303849 0
Viewed 173

I have a dataset with timestamp strings like '2022-05-25T13:31:22.566-0400' I would like to convert it to '%Y-%m-%dT%H:%i:%s' format but adjusting for the timezone difference.

So for the above, how convert '2022-05-25T13:31:22.566-0400' to '2022-05-25T17:31:22.566' in Presto?

Thanks a lot!

presto timezone-offset timezone timestamp sql
1 Answers

You can use from_iso8601_timestamp to parse date, then cast to timestamp to remove timezone info (will be treated as UTC, at time zone 'UTC' should have the same effect) and use date_format to get required output format:

select date_format(
        cast(from_iso8601_timestamp('2022-05-25T13:31:22.566-0400') as timestamp),
        '%Y-%m-%dT%H:%i:%s')

Or

select date_format(
    from_iso8601_timestamp('2022-05-25T13:31:22.566-0400') at time zone 'UTC', 
    '%Y-%m-%dT%H:%i:%s')

Output:

_col0
2022-05-25T17:31:22
user_25012790
Related