Issues with SQL Date Filtering

Hello,
I am currently trying to use a SQL query to pull 14 days of data from the Elasticsearch instance. I have attempted using many different date calculation for my where clause but they all result in errors.

All of the below are within the WHERE statement

adddate(@timestamp, INTERVAL 7 day) with the error [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Invalid SQL query: Failed to parse query due to offending symbol [7] at …

adddate(@timestamp, INTERVAL ‘7’ day) with the error [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Invalid SQL query: Failed to parse query due to offending symbol [‘7’] at …

@timestamp BETWEEN CURDATE()-7 AND CURDATE() with the error
ERROR [42000] [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Invalid SQL query: Failed to parse SqlExpression of type class com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr. expression value: CURDATE() - 7

@timestamp >= DATEADD(day, -7, @timestamp) with the error ERROR [42000] [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Invalid SQL query: Function [DATEADD] cannot be found or used here. Did you mean [DATE]?

@timestamp >= CURDATE() - 14 with the error ERROR [42000] [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Invalid SQL query: Operator [>=] cannot work with [DATE, INTEGER]

However, if i set the date in my where clause as WHERE @timestamp >= ‘2020-01-07’ the query works without any issues.

Is there a way for me to query the past 14 days without having to manually change the date every day?

Currently running Elasticsearch 7.8.0 and the ODBC is version 1.11

Thank You

Hi @rc12, sorry for the confusion. I guess this is because you didn’t enable the new SQL engine for this date function and interval type support: GitHub - opendistro-for-elasticsearch/sql: 🔍 Open Distro SQL Plugin. This would be avoided once this new engine released later and enabled by default. Thanks!

Very helpful - thank you for the reply

@rc12 Sure, no problem! I’m also updating our README to clarify the docs only available to the new SQL engine: Support NULLS FIRST/LAST in new engine by dai-chen · Pull Request #843 · opendistro-for-elasticsearch/sql · GitHub. Thanks!