No 'Time with time zone' data type in AWS Glue Crawler

Question

I have a PostgreSQL database created in Amazon RDS. In that database, there is table with a column whose data type is 'time with time zone'. When I crawl this table using AWS Glue Crawler, this column's data type is shown as 'timestamp'. When I try to edit the schema, there is no 'time' datatype in the list. What should I do to solve the issue? Any help is appreciated.

Thank you

Answer

Timestamp with Time Zone is not supported by the glue crawler or by Athena.

Timestamp would be automatically identified if it is in the java.sql.Timestamp format, i.e. yyyy-mm-dd hh:mm:ss[.f...] by the Glue crawler. see this link for timestamp format: 
https://docs.aws.amazon.com/athena/latest/ug/data-types.html

option 1 - You could use CAST() to convert string with timezone into timestamp

option 2 - you could write an custom classifier. refer to https://docs.aws.amazon.com/glue/latest/dg/custom-classifier.html for TIMEZONE and DATESTAMP

No 'Time with time zone' data type in AWS Glue Crawler

Relevanter Inhalt