Why is the TIMESTAMP result empty when I query a table in Amazon Athena?
"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."
"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.Short descriptionThe TIMESTAMP data might be in the wrong format. Athena requires the Java TIMESTAMP format:YYYY-MM-DD HH:MM:SS.fffffffffTo change TIMESTAMP data to the correct format:Define the column as STRING.Use Presto's date and time functions to read the column as DATE or TIMESTAMP in your query.ResolutionDefine the column as STRINGRun 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 queryIf your input data is in the following format:20200921T01500520200923T095605Then use the date_parse function as shown in the following example:SELECT date_parse(ts,'%Y%m%dT%h%i%s') FROM timestamptestcsv3Note: The format in the date_parse(string,format) function must be the TIMESTAMP format that's used in your data.If your input data is in ISO 8601 format, as in the following:2020-11-14T02:34:48ZThen use the from_iso8601_timestamp() function to read the ts field as TIMESTAMP. Example:SELECT from_iso8601_timestamp(ts) FROM timestamptestcsv3Related informationData types in Amazon AthenaFollow"