AWS Athena SYNTAX_ERROR: not a valid timestamp literal

Question

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?

Piotr Findeisen · Accepted Answer · 2019-04-14 20:34:36Z

3

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.

answered Apr 14, 2019 at 20:34

Piotr Findeisen

20.9k4 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oron Bendavid Over a year ago

Thanks! the following syntax is working: SELECT * FROM tests WHERE startDate BETWEEN from_unixtime(1555246800) AND from_unixtime(1555249497) limit 10;

Collectives™ on Stack Overflow

AWS Athena SYNTAX_ERROR: not a valid timestamp literal

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related