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