Why is the TIMESTAMP result empty when I query a table in Amazon Athena?

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 convert TIMESTAMP data to the correct format, complete the following steps:

1. Define the column as STRING type.
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 type

To store your timestamp data in a STRING type column, 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

Use the date_parse function from Presto to select STRING data as TIMESTAMP data. You must specify how the date_parse function parses the STRING data. For example, if you have the input STRING value 20200921T015005, then you can select the STRING data as TIMESTAMP data with the following query:

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

Note: The format in the date_parse(string, format) function must be in the same TIMESTAMP format as your data.

If your input data is in ISO 8601 format, such as 2020-11-14T02:34:48Z, then use the from_iso8601_timestamp function to read the ts field TIMESTAMP. For example, run the following query:

SELECT from_iso8601_timestamp(ts) FROM timestamptestcsv3

Related information

Data types in Amazon Athena