CODEWITHSUNDEEP
timestampamazon-athenapresto
Oron Bendavid
Oron Bendavid

Reputation: 1533

AWS Athena SYNTAX_ERROR: not a valid timestamp literal

I'm trying to run the following query:

SELECT startDate
FROM tests
WHERE startDate
    BETWEEN TIMESTAMP '1555248497'
        AND TIMESTAMP '1555248498' limit 10;

And keep getting this error message:

**"SYNTAX_ERROR: line 4:13: '1555248497' is not a valid timestamp literal"**

I've also tried removing the quotation marks around the timestamp, and got the following error:

line 4:23: extraneous input '1555248497' expecting {'.', '[', 'at', 'and', '+', '-', '*', '/', '%', '||'} (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: 44ad270d-54e4-442b-8b1a-93a6b6eba9ac)

The type of "startedDateTime" column is "timestamp".

How can I cast the timestamp values to a valid timestamp literal?

Upvotes: 3

Views: 5880

Answers (1)

Piotr Findeisen
Piotr Findeisen

Reputation: 20770

timestamp literals are of the form like this: 

TIMESTAMP '2001-08-22 03:04:05.321'

To convert UNIX timestamp (number of seconds since 1970-01-01 00:00 UTC) to timestamp data type, use from_unixtime.

Upvotes: 3

Related Questions