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

Question

I have a restaurant index which has a nested field that contains dayOfWeek, opening_hour and closing_hour hour_range(HH:mm:ss).

       "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:

                            Opening Hour     Start Range         End Range              Closing Hour

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

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

Answer 1

Since there doesn't seem to be easy and straightforward way to store hour range over 2 days, I added one more field hour_range of type integer range to solve the problem. This way, I can easily distinguish range of overflowing day by adding one extra day if end range is lower than start range.

For example, range: 23:00 - 03:00 would be stored as 82800 - 97200 representing the number of seconds in a day

range: 01:00 - 05:00 would be stored as 3600 - 18000