How can i filter time frequency in Elastic search?

Question

Consider the following situation, I have two menu- Burger and Pizza which are available as follows-

 item  | Week day |startTime(UTC)| end Time(UTC)
----------------------------------------
Burger | Sunday   | 8:00         |  11:00
Burger | Monday   | 7:00         |  11:00
Burger | Tuesday  | 7:00         |  11:00
Pizza  | Sunday   | 8:00         |  11:00
Pizza  | Monday   | 7:00         |  11:00
Pizza  | Tuesday  | 7:00         |  11:00

I have three branch B1, B2, B3 in timezone (GMT+4) and two branch B4, B5 in timezone (GMT+6).Consider all item is available in all branches. How can i store the above data and made query in elastic search so that i can get the available item with branch in current time based on user timezone?

Answer 1

You could combine a set of time-zoned range musts in a top-level should like so:

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "range": {
                  "startTime": {
                    "time_zone": "+04:00",
                    "gte": "08:00:00"
                  }
                }
              },
              {
                "range": {
                  "endTime": {
                    "time_zone": "+04:00",
                    "lte": "11:00:00"
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "range": {
                  "startTime": {
                    "time_zone": "+06:00",
                    "gte": "08:00:00"
                  }
                }
              },
              {
                "range": {
                  "endTime": {
                    "time_zone": "+06:00",
                    "lte": "11:00:00"
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Be aware, though, that storing time ranges like opening hours without the date (so just time instead of datetime) will likely result in misunderstandings as addressed here. There are better approaches to solving opening-times problems as discussed here, for example.