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

3 minute read
0

When I query a column of TIMESTAMP data in my Amazon Athena table, I get an exception.

Short description

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

  • TYPE_MISMATCH: line 1:49: Cannot apply operator: timestamp(3) < 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 cast a column with a data type that's not supported by Athena.

Resolution

Syntax errors with logical operators

If your query uses a logical operator to compare timestamp values, then you might get an error message like the following: "TYPE_MISMATCH: line 1:49: Cannot apply operator: timestamp(3) < varchar(19)".

The TIMESTAMP data in your table might be in the wrong format. Athena requires the Java TIMESTAMP format. Use Trino's date and time function to convert the 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 in the documentation on the Trino website.

  1. Create a table testdataset1 with timestamp data in STRING format:

    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 with the CAST function on the STRING value:

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

Invalid argument errors with CAST

If you use CAST to convert a column to TIMESTAMP data type, you might get an error message like the following: "Exception: cast(col as timestamp) with INVALID_CAST_ARGUMENT".

The data type of the column that you want cast to TIMESTAMP might be a type that isn't supported by Athena. Use Trino'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'),
            (3, 'c', '8/13/2017 4:22:39 AM')
    ) AS t(testid2, testname2, testtimestamp2)
  2. Convert the STRING column of timestamp data testtimestamp2 to JAVA TIMESTAMP format using Trino's date_parse function:

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

Related information

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

AWS OFFICIAL
AWS OFFICIALUpdated 3 months ago