Date and elasticsearch

So today I was trying to find the best way to store the opening hours for any vendor in the db(elasticsearch). I found out about the really flexible support given by elasticsearch.

Storing

Elastic search has a special type data which can help es to know that its a date field and to handle it differently. Also the date type has one more attribute called format which help us to specify format of the date. There are many formats supported by the es, about 43 different formats. This is just the part of storing data.

Searching

As the main job of ES is to search, it doesn’t stop here, it provides special data math capability to use in range queries.

As stated:

The expression starts with an anchor date, which can be either now or a date string (in the applicable format) ending with ||. It can then follow by a math expression, supporting +, - and / (rounding). The units supported are y (year), M (month), w (week), d (day), h (hour), m (minute), and s (second). Here are some samples: now+1h, now+1h+1m, now+1h/d, 2012-01-01||+1M/d.

To know more about it see Ranges on date fields

Opening hours

This SO question gives the perfect solution for this.

The mappings will be stored like this:

/PUT http://localhost:9200/demo/test/_mapping
{
  "test": {
    "properties": {
      "openingTimes": {
        "type": "object",
        "properties": {
          "monday": {
            "type": "nested",
            "properties": {
              "start": {
                "type": "date",
                "format": "hour_minute"
              },
              "end": {
                "type": "date",
                "format": "hour_minute"
              }
            }
          }
        }
      }
    }
  }
}

Then we can do the nested query:

/POST http://localhost:9200/demo/test/_search
{
  "query": {
    "filtered": {
      "query": {
        "match_all": {}
      },
      "filter": {
        "nested": {
          "path": "openingTimes.monday",
          "filter": {
            "bool": {
              "must": [
                {
                  "range": {
                    "openingTimes.monday.start": {
                      "lte": "13:00"
                    }
                  }
                },
                {
                  "range": {
                    "openingTimes.monday.end": {
                      "gte": "14:00"
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  }
}

Using Java

If you are using Java then you might need Joda-LocalTime to store time in Java and SimpleDateFormat for parsing. Also when using SimpleDateFormat don’t forget to set the timeZone setTimeZone

Storing

Searching

Opening hours

Using Java

Reference