How to use value from elasticsearch index as parameter in ES SQL functions, like DATE_DIFF()

[burakka (Burak)]

Hello guys!

I want to use Elastic SQL with functions in Kibana Canvas. Therefore I tried to use the function DATE_DIFF(), with values from an existing Elasticsearch index as parameter in the sql function.

I looked at the example from the documentation, but the examples are with concrete values as string:
https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-datetime.html#sql-functions-datetime-diff

I tested this query with an subquery in Kibana Dev Tools Console but I got an error.

POST /_sql?format=txt
{
  "query":"SELECT DATE_DIFF('days', (SELECT timestamp1 FROM index_name WHERE something = 'Lorem')::datetime, '2020-12-04'::date)"

Error:

{
  "error" : {
    "root_cause" : [
      {
        "type" : "unsupported_operation_exception",
        "reason" : null
      }
    ],
    "type" : "unsupported_operation_exception",
    "reason" : null
  },
  "status" : 500
}

Thank you for your help!!

Kind regards.

[bogdan.pintea]

Hello @burakka,
You might want to rephrase your query like:

SELECT DATE_DIFF('days', timestamp1, '2020-12-04'::date) FROM index_name  WHERE something = 'Lorem'

.