How to compare hours on elasticsearch

Question

I am trying to compare hours using painless language in my elasticsearch query. I would like query something like:

{  
   "script":"doc['schedule.from_time'] >= doc['schedule.to_time']"
}

But I have the error:

Cannot apply [>] operation to types [org.elasticsearch.index.fielddata.ScriptDocValues.Dates]

The scheme of the nested document is:

{
    "settings": {
        "index.mapping.total_fields.limit": 10000
    },
    "mappings": {
        "_doc": {
           "dynamic_templates": [{
                "integers": {
                    "match_mapping_type": "long",
                    "mapping": {
                        "type": "long",
                        "index": false
                    }
                }
            }],
            "properties": {
                "enabled_services": {
                    "type": "nested",
                    "properties": {
                        "service_id": {
                           "type": "text",
                           "analyzer": "whitespace",
                           "search_analyzer": "whitespace"
                        },
                        "available_day_of_week": {
                            "type": "long"
                        },
                        "available_from_time": {
                            "type": "date",
                            "format": "hour_minute"
                        },
                        "available_to_time": {
                            "type": "date",
                            "format": "hour_minute"
                        }
                    }
                }
            }
        }
    }
}

(The values are formated like "2:00" or "18:00").

I have tried to use .date or .value but it does not work because my variable contains only hours not datetime.

Can someone help me :)

Piotr Pradzynski · Answer 1 · 2018-11-07T13:54:45.620

I think you are looking for:

doc['enabled_services.available_from_time'].value.isAfter(doc['enabled_services.available_to_time'].value)

You also need to remove "type": "nested" from your mapping. I think it is not needed in your case.

Working code is below:

Mapping

PUT /painless-dates
{
  "settings": {
    "index.mapping.total_fields.limit": 10000
  },
  "mappings": {
    "_doc": {
      "dynamic_templates": [
        {
          "integers": {
            "match_mapping_type": "long",
            "mapping": {
              "type": "long",
              "index": false
            }
          }
        }
      ],
      "properties": {
        "enabled_services": {
          "properties": {
            "service_id": {
              "type": "text",
              "analyzer": "whitespace",
              "search_analyzer": "whitespace"
            },
            "available_day_of_week": {
              "type": "long"
            },
            "available_from_time": {
              "type": "date",
              "format": "hour_minute"
            },
            "available_to_time": {
              "type": "date",
              "format": "hour_minute"
            }
          }
        }
      }
    }
  }
}

Add two elements

POST /painless-dates/_doc
{
  "enabled_services": {
    "available_from_time": "02:00",
    "available_to_time": "18:00"
  }
}

POST /painless-dates/_doc
{
  "enabled_services": {
    "available_from_time": "04:00",
    "available_to_time": "03:00"
  }
}

Query

GET /painless-dates/_search
{
  "query": {
    "bool": {
      "must": {
        "script": {
          "script": {
            "source": "doc['enabled_services.available_from_time'].value.isAfter(doc['enabled_services.available_to_time'].value)",
            "lang": "painless"
          }
        }
      }
    }
  }
}

Answer

{
  "took": 8,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 1,
    "hits": [
      {
        "_index": "painless-dates",
        "_type": "_doc",
        "_id": "0wFw7mYBueYINcTmJsMG",
        "_score": 1,
        "_source": {
          "enabled_services": {
            "available_from_time": "04:00",
            "available_to_time": "03:00"
          }
        }
      }
    ]
  }
}

Unfortunately it does not work. When I display `doc['to_time'].value` whatever the value is, elasticsearch displays `"1970-01-01T00:00:00.000Z"` So, I cannot compare. What you suggested `doc['from_time'].value.isAfter(doc['to_time'].value)` always returns false, even with I switch from and to. — Jérémy, Nov 06 '18 at 14:23
I updated the answer. The additional issue was unnecessary `"type": "nested"` in your mapping. — Piotr Pradzynski, Nov 07 '18 at 13:55

score 0 · Answer 2 · answered Nov 08 '18 at 10:45

0

OK I found the answer:

{
    "script": {
        "script": "doc['enabled_services.available_from_time'].date.isBefore(doc['enabled_services.available_to_time'].date)"
    }
}

Thank you all !

answered Nov 08 '18 at 10:45

Jérémy

111
6

How to compare hours on elasticsearch

2 Answers2

Mapping

Add two elements

Query

Answer