CODEWITHSUNDEEP
timefilteringelasticsearch
mr1031011
mr1031011

Reputation: 3812

Filtering search results by operating hour with elasticsearch

I'm using elasticsearch to index and search locations, and I'm running into 1 particular issue with filtering by operating hour which I don't know how to work out

Basically, each location will have operating hour (for every day of the week) and each day may have more than 1 "sets" of operating hour (we use 2 for now).

For example: Monday: open 9am / close 12pm open 1pm / close 9pm

Given the current time and the current day of the week, I need to search for the "open" locations.

I don't know how should I index these operating hour together with the location details, and how to use them to filter out the results yet, any help, suggestion would be really appreciated

Regards

Upvotes: 7

Views: 4998

Answers (3)

Commander
Commander

Reputation: 1322

The above solution doesn't work because if you have something that is open 2-4 on monday and 6-8 on tuesday then doing a filter on monday at 6 will return the document. Below is some pseudojson to illustrate how it should be done.

{
    "business_document": "...",
    "hours": {
        "1": [
            {
                "open": 930,
                "close": 1330
            },
            {
                "open": 1530,
                "close": 2130
            }
        ],
        "2": [
            {
                "open": 1000,
                "close": 2100
            }
        ],
        "3": [
            {
                "open": 1000,
                "close": 2100
            }
        ],
        "4": [
            {
                "open": 1000,
                "close": 2100
            }
        ],
        "5": [
            {
                "open": 1000,
                "close": 2100
            }
        ],
        "6": [
            {
                "open": 1000,
                "close": 2100
            }
        ],
        "7": [
            {
                "open": 930,
                "close": 1330
            },
            {
                "open": 1530,
                "close": 2130
            }
        ]
    }
} 


Sample Filter (can be applied to any query for a businesses): 
{ 
    "filter": { 
        "and": [ //Must match all following clauses 
            { 
                "range": { 
                    "hours.1.open": { //Close Hour of Day 1 (current day) 
                        "lte": 1343 //Store open time is less than 13:43 (current time) 
                    } 
                } 
            }, 
            { 
                "range": { 
                    "hours.1.close": { //Close Hour of Day 1 (current day) 
                        "gte": 1343 //Store close time is greater than 13:43 (current time) 
                    } 
                } 
            } 
        ] 
    } 
}

All times should be in 24 hour format using a standard timezone (GMT)

Upvotes: 2

DrTech
DrTech

Reputation: 17319

A better way to do this would be to use nested documents.

First: set up your mapping to specify that the hours document should be treated as nested:

curl -XPUT 'http://127.0.0.1:9200/foo/?pretty=1'  -d '
{
   "mappings" : {
      "location" : {
         "properties" : {
            "hours" : {
               "include_in_root" : 1,
               "type" : "nested",
               "properties" : {
                  "open" : {
                     "type" : "short"
                  },
                  "close" : {
                     "type" : "short"
                  },
                  "day" : {
                     "index" : "not_analyzed",
                     "type" : "string"
                  }
               }
            },
            "name" : {
               "type" : "string"
            }
         }
      }
   }
}
'

Add some data: (note the multiple values for opening hours)

curl -XPOST 'http://127.0.0.1:9200/foo/location?pretty=1'  -d '
{
   "name" : "Test",
   "hours" : [
      {
         "open" : 9,
         "close" : 12,
         "day" : "monday"
      },
      {
         "open" : 13,
         "close" : 17,
         "day" : "monday"
      }
   ]
}
'

Then run your query, filtering by the current day and time:

curl -XGET 'http://127.0.0.1:9200/foo/location/_search?pretty=1'  -d '
{
   "query" : {
      "filtered" : {
         "query" : {
            "text" : {
               "name" : "test"
            }
         },
         "filter" : {
            "nested" : {
               "path" : "hours",
               "filter" : {
                  "and" : [
                     {
                        "term" : {
                           "hours.day" : "monday"
                        }
                     },
                     {
                        "range" : {
                           "hours.close" : {
                              "gte" : 10
                           }
                        }
                     },
                     {
                        "range" : {
                           "hours.open" : {
                              "lte" : 10
                           }
                        }
                     }
                  ]
               }
            }
         }
      }
   }
}
'

This should work.

Unfortunately, in 0.17.5, it throws an NPE - it is likely to be a simple bug which will be fixed shortly. I have opened an issue for this here: https://github.com/elasticsearch/elasticsearch/issues/1263

UPDATE Bizarrely, I now can't replicate the NPE - this query seems to work correctly both on version 0.17.5 and above. Must have been some temporary glitch.

clint

Upvotes: 5

imotov
imotov

Reputation: 30163

The simplest way to do it is by naming and indexing time slots when a location is open. First, you need to come up with a schema that assigns a name to each time slot when location can be open. For example, thu17 may represent 5PM on Thursday. The location in your example should then be indexed with several fields "open" containing the following values: mon09, mon10, mon11, mon13, mon14, mon15, mon16, mon17, mon18, mon19, mon20, tue09, tue10 and so on. To show only locations that are open on Thursday 7AM, you just need add this filter to your query: open:thu07.

You don't have to use this particular naming schema. You can, for example, just count the number of hours from the beginning of the week. In this case, 9 AM on Monday would be 9, 11PM on Monday - 23, 2AM on Tuesday - 26 and so on.

Upvotes: 0

Related Questions