1

I created a partition projection in Athena named 'paritiondate', which contains date information in the format 2022/01/01.

I'm running the following query in Athena (via a grafana plugin) which make use of a grafana/athena macro $__dateFilter. The macro is necessary because it detects the date range in Grafana and updates the BETWEEN clause in the Athena query accordingly:

SELECT ssl_protocol, count (*) FROM alb_logs_partition WHERE $__dateFilter(partitiondate) LIMIT 5

The resulting raw query received by Athena is:

SELECT ssl_protocol, count (*) FROM alb_logs_partition WHERE partitiondate BETWEEN date '2022-01-10' AND date '2022-01-11' LIMIT 5

However, I'm receiving the following error:

error querying the database: SYNTAX_ERROR: line 1:62: Cannot check if varchar is BETWEEN date and date.

So my understanding is that Athena is rejecting the partitiondate value as it's a varchar, and not date.

I've tried to find a workaround in an attempt to convert it into a date format using date_parse. For example I tried:

SELECT ssl_protocol, count (*) FROM alb_logs_partition WHERE $__dateFilter(date_parse(partitiondate, '%Y/%m/%d')) LIMIT 5

However, the macro cannot handle this and throws an error.

Is there any other way I can modify the query to convert partitiondate from a varchar into a format Athena/Presto understands, before then using the value in the $__dateFilter macro?

Improve this question
3
  • "contains date information is in the format 01/01/2022" .... Why are you using different format '%Y/%m/%d' ? Commented Jan 12, 2022 at 13:57
  • Sorry, that was a typo, I've corrected it, thanks for pointing that out. Commented Jan 12, 2022 at 16:50
  • 1
    I found answer. Please, check this link Commented Dec 29, 2022 at 14:30
Related questions
61
Athena greater than condition in date column
36
AWS Athena SQL Query Error with timestamp
8
AWS Athena query error when trying to filter by date
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.