timestamp with time zone doesn't work

[VDFaller]

how to replicate

create a sql file

--test.sql
SELECT CAST('2022-07-20 9:15:00 US/Pacific' AS Timestamp with time zone) as dt

run

dbt run --select test

error

Invalid column type for column dt: Unsupported Hive type: timestamp with time zone

[owenprough-sift]

Hive only supports timestamps without time zones (docs), but Athena has some extra timezone functions available (docs).

[VDFaller]

@owenprough-sift I'm confused what you're telling me (unless you're not talking to me and just putting in some debug notes). Because that same sql file runs fine when I just run it on athena.

[owenprough-sift]

Your sample query runs fine as a SELECT statement, but not in a CREATE statement, which is what dbt-athena runs. You can try it yourself in the Athena web console:

CREATE TABLE tz_table AS SELECT CAST('2022-07-20 9:15:00 US/Pacific' AS Timestamp with time zone) as dt

You'll get the error.

Until Athena/Hive adds support for timezone-aware timestamp datatype, the workaround is probably to use the timestamp datatype (and store your timestamps in UTC) and convert them to the desired timezone on-the-fly as necessary.

[VDFaller]

OH, gotcha. Thanks for the explaination.

[VDFaller]

Closing this as it seems it's more an athena issue than dbt