When I query a table in Amazon Athena, the TIMESTAMP result is empty

Last updated: 2020-11-17

When I query a column of TIMESTAMP data in my Amazon Athena table, I get empty results or the query fails. The data exists in the input file. How do I resolve this?

Short description

The TIMESTAMP data might be in the wrong format. Athena requires the Java TIMESTAMP format:

YYYY-MM-DD HH:MM:SS.fffffffff

To change TIMESTAMP data to the correct format:

  1. Define the column as STRING.
  2. Use Presto's date and time functions to read the column as DATE or TIMESTAMP in your query.

Resolution

Define the column as STRING

Run a command similar to the following:

CREATE EXTERNAL TABLE 'test'(
  'ts' STRING)ROW FORMAT SERDE<  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
LOCATION
  's3://doc-example-bucket/path-to-data/'

Use Presto's date and time functions to read the column as DATE or TIMESTAMP in your query

If your input data is in the following format:

20200921T015005
20200923T095605

Then use the date_parse function as shown in the following example:

SELECT date_parse(ts,'%Y%m%dT%h%i%s') FROM timestamptestcsv3

Note: The format in the date_parse(string,format) function must be the TIMESTAMP format that's used in your data. For more information, see MySQL date functions in the Presto documentation.

If your input data is in ISO 8601 format, as in the following:

2020-11-14T02:34:48Z

Then use the from_iso8601_timestamp() function to read the ts field as TIMESTAMP. Example:

SELECT from_iso8601_timestamp(ts) FROM timestamptestcsv3

Did this article help?


Do you need billing or technical support?