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

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:

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" : {
    "root_cause" : [
        "type" : "unsupported_operation_exception",
        "reason" : null
    "type" : "unsupported_operation_exception",
    "reason" : null
  "status" : 500

Thank you for your help!!

Kind regards.

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'


1 Like

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.