Calculate time difference between 2 date fields : scripted field

Elastic Stack Elasticsearch
1

HI,

I need to calculate duration using 2 date fields in watcher API. I tried using painless script and to test using scripted field. But it is errors out stating invalid syntax. What is missing in below ?

if (!doc.containsKey('job_end_date') || doc['job_end_date'].empty))
{(doc['job_end_date'].value.toInstant().toEpochMilli() - doc['job_start_date'].value.toInstant().toEpochMilli())
}
else
{ return 0; }

2

Firstly, I get an exception since newlines are not allowed in the JSON source. Put it all on one line.

I can't try this in Watcher right now. But here's something that works against the Painless Execute API as if I were running it in a function_score query. I'm just using the context because it's the only one that supports reading from a doc and returning a number - you're trying to return 0 or number of milliseconds. You may need to adjust slightly for Watcher!

Do these in the Kibana Dev Console to try for yourself:

PUT myindex

PUT myindex/_mapping
{
  "properties" : {
    "job_end_date" : {
      "type" : "date"
    },
    "job_start_date" : {
      "type" : "date"
    }
  }
}

POST /_scripts/painless/_execute
{
  "script": {
    "source": "if (doc.containsKey('job_start_date') && doc['job_start_date'].size() != 0 && doc.containsKey('job_end_date') && doc['job_end_date'].size() != 0) { doc['job_end_date'].value.toInstant().toEpochMilli() - doc['job_start_date'].value.toInstant().toEpochMilli() } else { 0; }"
  },
  "context": "score",
  "context_setup": {
    "index": "myindex",
    "document": {
      "job_start_date" : "2020-01-29T04:33:32Z",
      "job_end_date" : "2020-01-29T05:33:32Z"
    }
  }
}

You should get

{
  "result" : 3600000.0
}

In addition to the newlines I've:

  • removed the return in the else
  • changed the conditional to check both date fields exist and that they're not empty (the recommended way is via .size() != 0). I think this is what you were trying to do but correct me if wrong.
1 Like
3

Thank you . That works.
If end date doesn't exist, I need to calculate (now - start date)

4

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