CODEWITHSUNDEEP
amazon-athenatrinoapache-iceberg
Guilherme Noronha
Guilherme Noronha

Reputation: 630

AWS Athena with Iceberg: convert timestamp string with timezone into a timestamp column

I have the following string:

2024-02-28T14:26:51.292534-03:00

I want to convert it to a timestamp column without losing any data. I tried different solutions such as the following, but none worked:

Which is the best way to convert it? Here is the desired output for my request:

data: 2024-02-28 17:26:51.292534 UTC

typeof: timestamp(6) with time zone

Upvotes: 0

Views: 295

Answers (1)

Jason Crease
Jason Crease

Reputation: 1975

from_iso8601_timestamp('2024-02-28T14:26:51.292534-03:00') AT TIME ZONE 'UTC'

gives you what you want:

2024-02-28 17:26:51.292000 UTC

But since you want a timestamp(6) with time zone, in total you'll need:

CAST(from_iso8601_timestamp('2024-02-28T14:26:51.292534-03:00') AT TIME ZONE 'UTC' AS timestamp(6) WITH TIME ZONE)

To check a type, do:

SELECT typeof(thing above)

Upvotes: 0

Related Questions