How can I resolve timestamp exceptions when I query a table in Amazon Athena?

Short description

When you query an Athena table with TIMESTAMP data, your query might fail with either of the following exceptions:

SYNTAX_ERROR: line '>' cannot be applied to timestamp, varchar(19): You might get this exception if you used a logical operator, such as '>', between TIMESTAMP and STRING values in your query.

cast(col as timestamp) with INVALID_CAST_ARGUMENT: You might get this exception if you use casting on a column with the data type that's not supported by Athena.

Resolution

Exception: SYNTAX_ERROR: line '>' cannot be applied to timestamp, varchar(19)

The TIMESTAMP data in your table might be in the wrong format. Athena requires the Java TIMESTAMP format. Use Presto's date and time function or casting to convert the STRING to TIMESTAMP in the query filter condition. For more information, see Date and time functions and operators in the Presto documentation.

1.    Create a table testdataset1 by running a query similar to the following:

CREATE TABLE testdataset1 AS SELECT testid1, testname1, date_parse(testtimestamp1,'%m/%d/%Y %h:%i:%s %p') AS testtimestamp1 
FROM (
    VALUES
        (1, 'a','7/14/2020 8:22:39 AM'),
        (2, 'b','8/01/2015 10:22:39 PM'),
        (3, 'c','8/13/2017 4:22:39 AM')
) AS t(testid1, testname1, testtimestamp1)

2.    Apply the timestamp filter by using the cast function on the literal value (example: 2020-07-14 00:00:00):

SELECT * FROM testdataset WHERE testtimestamp1 < cast('2020-07-14 00:00:00' as timestamp)

Exception: cast(col as timestamp) with INVALID_CAST_ARGUMENT

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

1.    Create a table testdataset2 by running a query similar to the following:

CREATE TABLE testdataset2 AS SELECT * FROM 
(VALUES
        (1, 'a','7/14/2020 8:22:39 AM'),
        (2, 'b','8/01/2015 10:22:39 PM'),