Search code examples
prestoamazon-athena

Specifying timestamp or date format in AWS athena Table

I have a timestamp in ISO-8601 format and want to specify it either as a timestamp or datetime format when creating a table in Athena. Any clues on how to do this ?

Thanks!

Solution

  • When you create table in Athena you can set a column as date or timestamp only in the Unix format as follows:

    • DATE, in the UNIX format, such as YYYY-MM-DD.

    • TIMESTAMP. Instant in time and date in the UNiX format, such as yyyy-mm-dd hh:mm:ss[.f...]. For example, TIMESTAMP '2008-09-15 03:04:05.324'. This format uses the session time zone.

    If the format is different, define it as a String and when you query the data use the date function:

    from_iso8601_date(string) → date

    You can convert the data to make it easier and cheaper for specific use cases by using CTAS (create table as select) query that will generate a new copy of the data in a simpler and more efficient (compressed and columnar) parquet format. You can also make a view that parses the column.

    There are some more considerations around dates when it comes to partition keys. See also this overview of working with dates.