# How to filter elastic search document based on overflowing hour range?

**URL:** <https://discuss.elastic.co/t/how-to-filter-elastic-search-document-based-on-overflowing-hour-range/233751>\
**Category:** Elasticsearch\
**Created:** [May 21, 2020, 2:07pm UTC](https://discuss.elastic.co/t/how-to-filter-elastic-search-document-based-on-overflowing-hour-range/233751 "2020-05-21T14:07:43Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![f3l1xss](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/f3l1xss/32/55083_2.png) [@f3l1xss](https://discuss.elastic.co/u/f3l1xss)\
**Post date:** [May 21, 2020, 2:07pm UTC](https://discuss.elastic.co/t/how-to-filter-elastic-search-document-based-on-overflowing-hour-range/233751/1 "2020-05-21T14:07:43Z")

</div>

I have a restaurant index which has a nested field that contains dayOfWeek, opening\_hour and closing\_hour hour\_range(HH:mm:ss).

```auto
       "hours": { "type": "nested" ,
                             "properties": {
                                "day_of_week": {
                                    "type": "short"
                                },
                                "opening_hour": {
                                    "type": "date",
                                    "format": "strict_hour_minute_second"
                                },
                                "closing_hour": {
                                    "type": "date",
                                    "format": "strict_hour_minute_second"
                                },
                                "restaurant_id": {
                                    "type": "integer"
                                }
                            }
                    },

```

My requirement is that a restaurant could open till the next day meaning that it is valid for one restaurant to have opening hour less than closing hour (e.g: 22:00:00 - 02:00:00)

Based on that requirement, I will not be able to use elastic search date range field type because of the above and hence have to have separate fields for opening and closing hour.

Now, given above mapping, how could I query whether or not certain hour range falls between opening and closing hour?

Example:

```auto
                            Opening Hour Start Range End Range Closing Hour

```

```auto
1.Normal case 03:00 04:00 09:00 10:00

2.Overflowing opening hour 23:00 01:00 03:00 10:00

3.Overflowing closing hour 03:00 04:00 22:00 02:00

4.Overflowing range 22:00 23:00 03:00 04:00

```

All above four cases are valid and document SHOULD be returned.

In case #1 and #4 I simply have to find openingHours that is less than equal startRange AND closing hours greater than equal endRange

Now case#2 and #3 would require me to compare either the openingHour and startRange or closingHour and endRange and modify the query accordingly. Both would require me to access the indexed value openingHour and closingHour.

I thought of applying the logic through script query. However, I realized that in filter context [https://www.elastic.co/guide/en/elasticsearch/painless/master/painless-filter-context.html](https://www.elastic.co/guide/en/elasticsearch/painless/master/painless-filter-context.html)

\_source is not available, only doc values which prohibit us from accessing nested fields.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 18, 2020, 2:07pm UTC](https://discuss.elastic.co/t/how-to-filter-elastic-search-document-based-on-overflowing-hour-range/233751/2 "2020-06-18T14:07:45Z")

</div>

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