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

If the table is defined according to the input data format, the TIMESTAMP data is likely in the wrong format. Athena requires the Java TIMESTAMP format: "YYYY-MM-DD HH:MM:SS.fffffffff" (nine decimal place precision).

Note: If your data is in ISO 8601 format, use the from_iso8601_timestamp() function to convert the data to TIMESTAMP before you proceed.

If your data isn't in the required TIMESTAMP format, define the column as STRING, and then use Presto's date and time functions to read the field as DATE or TIMESTAMP in your query. For more information, see MySQL Date Functions in Presto Documentation.

Example:

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

The input file has the following data: 

20170621T015005
20170723T095605

To read the ts field as DATE or TIMESTAMP, you must use the date_parse function as follows:

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.

Output:

"2017-06-21 01:50:05.000"
"2017-07-23 09:56:05.000"

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2018-08-08