CODEWITHSUNDEEP
amazon-athenapresto
micah
micah

Reputation: 8096

Athena Table Timestamp With Time Zone Not Possible?

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?

Upvotes: 4

Views: 5078

Answers (2)

Felipe
Felipe

Reputation: 7563

Just to give a complete solution after @AswinRajaram answered that Athena does not support timestampo with timezone. Here is how one can CAST the timestamp from a string and use it with timezone.

select 
parse_datetime('2022-09-10_00', 'yyyy-MM-dd_H'),
parse_datetime('2022-09-10_00', 'yyyy-MM-dd_H') AT TIME ZONE 'Europe/Berlin',
at_timezone(CAST(parse_datetime('2022-09-10_00', 'yyyy-MM-dd_HH') AS timestamp), 'Europe/Berlin') AS date_partition_berlin,
CAST(parse_datetime('2022-09-10_00', 'yyyy-MM-dd_HH') AT TIME ZONE 'Europe/Berlin' AS timestamp) AS date_partition_timestamp;

2022-09-10 00:00:00.000 UTC
2022-09-10 02:00:00.000 Europe/Berlin // time zone conversion + 2 hours
2022-09-10 02:00:00.000 Europe/Berlin // time zone conversion + 2 hours
2022-09-10 00:00:00.000

Upvotes: 0

AswinRajaram
AswinRajaram

Reputation: 1622

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;

Upvotes: 3

Related Questions