Athena Table Timestamp With Time Zone Not Possible?

Question

I am trying to create an athena table with a timestamp column that has time zone information. The create sql looks something like this:

        CREATE EXTERNAL TABLE `tmp_123` (
`event_datehour_tz` timestamp with time zone
        )
        ROW FORMAT SERDE 
          'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
        STORED AS INPUTFORMAT 
          'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
        OUTPUTFORMAT 
          'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
        LOCATION
          's3://...'
        TBLPROPERTIES (
          'Classification'='parquet'
        )

When I run this, I get the error:

line 1:8: mismatched input 'external'. expecting: 'or', 'schema', 'table', 'view' (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: b7fa4045-a77e-4151-84d7-1b43db2b68f2; proxy: null)

If I remove the with time zone it will create the table. I've tried this and timestamptz. Is it not possible to create a table in athena that has a timestamp with time zone column?

Answer 1

Unfortunately Athena does not support timestamp with time zone.

What you may do is use the CAST() function around that function call, which will change the type from timestamp with time zone into timestamp.

Or, you can maybe save it as timestamp and use AT TIME STAMP operator as given below:

 SELECT event_datehour_tz AT TIME ZONE 'America/Los_Angeles' AS la_time;