AWS Athena SQL Query Error with timestamp

Question

I have a table in AWS Athena having column named 'servertime' with the data type of timestamp. I run a query like this

select * 
from table_name 
where servertime between '2018-04-01 00:00:00' and '2018-04-05 23:59:59';

It gives me this error: Your query has the following error(s): SYNTAX_ERROR: line 1:41: '=' cannot be applied to timestamp, varchar(19)

How can I resolve this in Athena? And It's important query to get the data from this table.

Answer 1

The problem you are seeing is related to your between condition. If you present the timestamp just as varchar, Athena will not convert those into timestamps.

For that to happen, you need to pass an explicit typecast.

select * from table_name 
where servertime 
   between TIMESTAMP '2018-04-01 00:00:00' 
   and TIMESTAMP '2018-04-05 23:59:59';