How could I compare these two time values using painless script?

image
I am using ES to analysis medical data. how can i using painless script compare these two time values(beginDateTime, endDateTime).deep values. if it's not deep value, i found a solution like this

{
  "query": {
    "bool": {
      "must": [
        {
          "script": {
            "script": "if (doc['basic.visitStartDateTime'].size()==0 || doc['basic.visitEndDateTime'].size()==0) {return false} else {return doc['basic.visitStartDateTime'].value.toInstant().toEpochMilli() >= doc['basic.visitEndDateTime'].value.toInstant().toEpochMilli()}"
          }
        }
      ]
    }
  }
}

What is the expected result you are look for from these two values? The documentation has multiple examples on doing datetime math and differences.

Hi Aron, thanks for your kindly reply, what puzzles me is the data structure, I don't know how to use the painless script in DSL to get these two values. In Kibana, Lucene is easy to get the values, but can't compare them.

I can help you out but need to know what the result from the 2 values you are looking for. Difference in days, minutes, seconds, etc?

Using the example you posted you have a beginDateTime and endDateTime. What's the new field you want created and the value of that field?

:sun_with_face: Goodmorning
I want to compare the beginDateTime and endDateTime exact to second (yyyy-mm-dd hh24:mi: ss).
If the endDateTime is before < the beginDateTime, returned all query records.
No need to create a new field, just use these query records in Kibana(Find records with time logic errors in medical data)

I believe this is what you are looking for.

POST peilin/_doc/1
{
  "id": 1,
  "beginDateTime": "2021-10-25T00:00:00",
  "endDateTime": "2021-10-24T00:00:00"
}

POST peilin/_doc/2
{
  "id": 2,
  "beginDateTime": "2021-10-25T00:00:00",
  "endDateTime": "2021-10-23T00:00:00"
}

POST peilin/_doc/3
{
  "id": 3,
  "beginDateTime": "2021-10-25T00:00:00",
  "endDateTime": "2021-10-26T00:00:00"
}

GET peilin/_search
{
  "query": {
    "bool": {
      "filter": {
        "script": {
          "script": """
            if (doc['endDateTime'].value.isBefore(doc['beginDateTime'].value)) {
              return true;
            }
            return false;
          """
        }
      }
    }
  }
}
1 Like

Hi Aron, thanks for your help. :grinning:

Now. I solved this problem. it's a nested layer query problem, I am not familiar with the solution of this kind matter before.
Here is my code. Hope it can be helpful to those who also have problems with query nested data

GET /a-case-l2-recent/_search
{
  "query": {
     "nested": {
      "path": "stay.dept4Ds.slices",
         "query": {
           "bool": {
              "must": [
                         {
                          "script": {
                             "script": {
                                "source": """
                                if(doc['stay.dept4Ds.slices.beginDateTime.keyword'].size()==0 || doc['stay.dept4Ds.slices.endDateTime.keyword'].size()==0){
                                  return false
                                }else{
                                if(doc['stay.dept4Ds.slices.beginDateTime.keyword'].value.compareTo(doc['stay.dept4Ds.slices.endDateTime.keyword'].value) <0){return false} else {return true }
                                }
                               """
                               }
                          }
                         }
              ]
           }
         }
     }
  }
}

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