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

Short description

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

• "TYPE_MISMATCH: line 1:49: Cannot apply operator: timestamp(3) < varchar(19)": If you use a logical operator, such as '<', between TIMESTAMP and STRING values in your query, then you might get this exception.
• "cast(col as timestamp) with INVALID_CAST_ARGUMENT": If you use CAST on a column with a data type that Athena doesn't support, then you might get this exception.

Resolution

Syntax errors with logical operators

If your query uses a logical operator to compare timestamp values, then you might get an exception error.

The TIMESTAMP data in your table might be in the wrong format. Athena requires the Java TIMESTAMP format. Use the Trino date and time function to convert STRING to TIMESTAMP in the query filter condition, or use CAST to convert STRING to TIMESTAMP. For more information, see Date and time functions and operators on the Trino website.

Complete the following steps:

1. Create a table with timestamp data in the STRING format, similar to the following example:

   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)

   Note: Replace testdataset1, testid1, testname1, and testtimestamp1 with your values.
2. Use the CAST function on the STRING value to apply the timestamp filter:

   SELECT * FROM testdataset1 WHERE testtimestamp1 < CAST('2020-07-14 00:00:00' AS timestamp)

   Note: Replace testdataset1 and testtimestamp1 with your values.

Invalid argument errors with CAST

If you use CAST to convert a column to the TIMESTAMP data type, then you might get an exception error.

The data type of the column that you want to cast to TIMESTAMP might be a type that Athena doesn't support. Use the Trino date and time functions to read the column as DATE or TIMESTAMP in your query. For more information, see Date and time functions and operators on the Trino website.

Complete the following steps:

1. Create a table similar to the following example:

   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'),
           (3, 'c', '8/13/2017 4:22:39 AM')
   ) AS t(testid2, testname2, testtimestamp2)

   Note: Replace testdataset2, testid2, testname2, and testtimestamp2 with your values.
2. Use the Trino date_parse function to convert your table to the Java TIMESTAMP format:

   SELECT testid2, testname2, date_parse(testtimestamp2, '%m/%d/%Y %h:%i:%s %p') AS testtimestamp2 FROM testdataset2

   Note: Replace testdataset2, testid2, testname2, and testtimestamp2 with your values.

Related information

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