28

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.

Ilya Kisil
  • 2,490
  • 2
  • 17
  • 31
Sumit Kumar Sagar
  • 381
  • 1
  • 3
  • 6

1 Answers1

62

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';
jens walter
  • 13,269
  • 2
  • 56
  • 54
  • 1
    Note also that in Presto (and so in Athena), timestamp has milliseconds precision, so you may want to use `TIMESTAMP '2018-04-05 23:59:59.999'` as the upper bound (or just ordinary `>= some_day AND < next_day` instead). – Piotr Findeisen Aug 10 '18 at 20:57
  • 2
    Maybe [this](https://aws.amazon.com/premiumsupport/knowledge-center/query-table-athena-timestamp-empty/) is helpful to someone. In my case I had to convert the timestamp to the right format (like `from_iso8601_timestamp(ts)`) for the condition to work. – Oleg Jul 02 '21 at 09:52