AWS Athena SQL Query Error with timestamp

sql timestamp amazon-athena presto
Sumit Kumar Sagar picture Sumit Kumar Sagar · Aug 9, 2018 · Viewed 18.8k times · Source

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

jens walter picture jens walter · Aug 9, 2018

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';

Related questions

Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

I want to extract just the date part from a timestamp in PostgreSQL. I need it to be a postgresql DATE type so I can insert it into another table that expects a DATE value. For example, if I have 2011/05/26 09:00:00, …

Read More
How to select rows that have current day's timestamp?

I am trying to select only today's records from a database table. Currently I use SELECT * FROM `table` WHERE (`timestamp` > DATE_SUB(now(), INTERVAL 1 DAY)); But this takes results for the last 24 hours, and I need it to only …

Read More
Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine

I have a sqlite (v3) table with this column definition: "timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP The server that this database lives on is in the CST time zone. When I insert into my table without including the timestamp column, sqlite …

Read More