# Interval Based Search in Elastic Search **URL:** \ **Category:** Elasticsearch\ **Created:** [December 10, 2013, 7:37pm UTC](https://discuss.elastic.co/t/interval-based-search-in-elastic-search/14798 "2013-12-10T19:37:57Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Bijuv\_V](https://avatars.discourse-cdn.com/v4/letter/b/b77776/32.png) [@Bijuv\_V](https://discuss.elastic.co/u/Bijuv_V)\ **Post date:** [December 10, 2013, 7:37pm UTC](https://discuss.elastic.co/t/interval-based-search-in-elastic-search/14798/1 "2013-12-10T19:37:57Z")
I have a search requirement , which Im trying to solve using Elastic Search. Assuming that the below JSON is indexed, what will be the query to fetch an availability with starttime as 10/12/2013 11:00:00 and time period of 35 minutes . The search should return only the MeetingRoom1. Im sure that I should be looking at date functions but not able to find a correct query criteria that can be used . Any pointers? Meeting Room 1: { "meetingroomid": 45, "Availablities": [ { "starttime": "10/12/2013 10:00:00" , "endtime": "10/12/2013 13:00:00" }, { "starttime": "10/12/2013 14:00:00" , "endtime": "10/12/2013 15:30:00" }, { "starttime": "10/12/2013 16:00:00" , "endtime": "10/12/2013 18:00:00" } ] } Meeting Room 2: { "meetingroomid": 100, "Availablities": [ { "starttime": "10/12/2013 10:00:00" , "endtime": "10/12/2013 11:01:00" }, { "starttime": "10/12/2013 11:05:00" , "endtime": "10/12/2013 12:30:00" }, { "starttime": "10/12/2013 16:00:00" , "endtime": "10/12/2013 18:00:00" } ] } -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![mattweber](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mattweber/32/44940_2.png) [@mattweber](https://discuss.elastic.co/u/mattweber)\ **Post date:** [December 10, 2013, 8:15pm UTC](https://discuss.elastic.co/t/interval-based-search-in-elastic-search/14798/2 "2013-12-10T20:15:47Z")
You will have to use a nested mapping [1] for the Availabilities field. Then you can do a nested filter [2] similar to the following: "constant\_score" : { "filter" : { "nested": { "path": "Availabilities", "filter": { "bool": { "must": [ {"term": {"Availabilities.starttime": "10/12/2013 11:00:00/m"}}, {"range": {"Availabilities.endtime": {"gte":"10/12/2013 11:00:00||+35m/m"}}} ] } } } } } Hope this helps. Thanks, Matt Weber [1] > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. [2] > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. On Tue, Dec 10, 2013 at 11:37 AM, Bijuv V [vvbiju2005@gmail.com](mailto:vvbiju2005@gmail.com) wrote: > I have a search requirement , which Im trying to solve using Elastic > Search. > > Assuming that the below JSON is indexed, what will be the query to fetch > an availability with starttime as 10/12/2013 11:00:00 and time period of 35 > minutes . The search should return only the MeetingRoom1. > > Im sure that I should be looking at date functions but not able to find a > correct query criteria that can be used . Any pointers? > > Meeting Room 1: > > { > "meetingroomid": 45, > "Availablities": > [ > { > "starttime": "10/12/2013 10:00:00" , > "endtime": "10/12/2013 13:00:00" > }, > { > "starttime": "10/12/2013 14:00:00" , > "endtime": "10/12/2013 15:30:00" > }, > { > "starttime": "10/12/2013 16:00:00" , > "endtime": "10/12/2013 18:00:00" > } > ] > } > > Meeting Room 2: > > { > "meetingroomid": 100, > "Availablities": > [ > { > "starttime": "10/12/2013 10:00:00" , > "endtime": "10/12/2013 11:01:00" > }, > { > "starttime": "10/12/2013 11:05:00" , > "endtime": "10/12/2013 12:30:00" > }, > { > "starttime": "10/12/2013 16:00:00" , > "endtime": "10/12/2013 18:00:00" > } > ] > } > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com) > . > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoDsr0q-bUkHb%3DkxH%2BhRYKZ2m5gspRZqeSX1KDe%2B\_dTViQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoDsr0q-bUkHb%3DkxH%2BhRYKZ2m5gspRZqeSX1KDe%2B_dTViQ%40mail.gmail.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Bijuv\_V](https://avatars.discourse-cdn.com/v4/letter/b/b77776/32.png) [@Bijuv\_V](https://discuss.elastic.co/u/Bijuv_V)\ **Post date:** [December 11, 2013, 10:41pm UTC](https://discuss.elastic.co/t/interval-based-search-in-elastic-search/14798/3 "2013-12-11T22:41:22Z")
Hi Matt I did the below steps and the search doesnt return the correct data HTTP PUT Request : [http://localhost:9200/avails/avail/\_mapping](http://localhost:9200/avails/avail/_mapping) { "avail" : { "properties" : { "id": {type:"long"}, "location" : {"type" : "string"}, "availablities" : { "type" : "nested", "properties": { ``` "endtime":{"type":"date", "format" : ``` "date\_hour\_minute\_second"}, "starttime":{"type":"date" , "format" : "date\_hour\_minute\_second"} ``` } } } } ``` } Inserted 2 records { avail: { "id": 1, "location" : "us", "availablities": [ { "starttime": "2013-12-10T10:00:00" , "endtime": "2013-12-10T13:00:00" }, { "starttime": "2013-12-10T14:00:00" , "endtime": "2013-12-10T15:30:00" }, { "starttime": "2013-12-10T16:00:00" , "endtime": "2013-12-10T18:00:00" } ] } } { avail: { "id": 2, "location" : "uk", "availablities": [ { "starttime": "2013-12-10T10:00:00" , "endtime": "2013-12-10T11:00:00" }, { "starttime": "2013-12-10T11:05:00" , "endtime": "2013-12-10T11:30:00" }, { "starttime": "2013-12-10T17:00:00" , "endtime": "2013-12-10T18:00:00" } ] } } Then performed the search [http://localhost:9200/avails/\_search](http://localhost:9200/avails/_search) {"query":{ "filtered": { "query": { "match\_all": {} }, "filter": { "nested": { "path": "availablities", "filter": { "bool": { "must": [ { "term": { "availablities.starttime": "2013-12-10T11:00:00/m" } }, { "range": { "availablities.endtime": { "gte": "2013-12-10T11:00:00||+35m/m" } } } ] } } } } } } } Now the issues - a. The starttime is not 11:00 AM in my data but its 10:00AM. So how does the equality match of starttime to 11:00AM match? b. when I tried the same, it complains that date is malformed at /m. Regards On Tue, Dec 10, 2013 at 9:15 PM, Matt Weber [matt.weber@gmail.com](mailto:matt.weber@gmail.com) wrote: > You will have to use a nested mapping [1] for the Availabilities field. > Then you can do a nested filter [2] similar to the following: > > "constant\_score" : { > "filter" : { > "nested": { > "path": "Availabilities", > "filter": { > "bool": { > "must": [ > {"term": {"Availabilities.starttime": "10/12/2013 > 11:00:00/m"}}, > {"range": {"Availabilities.endtime": > {"gte":"10/12/2013 11:00:00||+35m/m"}}} > ] > } > } > } > } > } > > Hope this helps. > > Thanks, > Matt Weber > > [1] > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html) > [2] > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/query-dsl-nested-filter.html) > > On Tue, Dec 10, 2013 at 11:37 AM, Bijuv V [vvbiju2005@gmail.com](mailto:vvbiju2005@gmail.com) wrote: > > > I have a search requirement , which Im trying to solve using Elastic > > Search. > > > > Assuming that the below JSON is indexed, what will be the query to fetch > > an availability with starttime as 10/12/2013 11:00:00 and time period of 35 > > minutes . The search should return only the MeetingRoom1. > > > > Im sure that I should be looking at date functions but not able to find a > > correct query criteria that can be used . Any pointers? > > > > Meeting Room 1: > > > > { > > "meetingroomid": 45, > > "Availablities": > > [ > > { > > "starttime": "10/12/2013 10:00:00" , > > "endtime": "10/12/2013 13:00:00" > > }, > > { > > "starttime": "10/12/2013 14:00:00" , > > "endtime": "10/12/2013 15:30:00" > > }, > > { > > "starttime": "10/12/2013 16:00:00" , > > "endtime": "10/12/2013 18:00:00" > > } > > ] > > } > > > > Meeting Room 2: > > > > { > > "meetingroomid": 100, > > "Availablities": > > [ > > { > > "starttime": "10/12/2013 10:00:00" , > > "endtime": "10/12/2013 11:01:00" > > }, > > { > > "starttime": "10/12/2013 11:05:00" , > > "endtime": "10/12/2013 12:30:00" > > }, > > { > > "starttime": "10/12/2013 16:00:00" , > > "endtime": "10/12/2013 18:00:00" > > } > > ] > > } > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com) > > . > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > -- > You received this message because you are subscribed to a topic in the > Google Groups "elasticsearch" group. > To unsubscribe from this topic, visit > [https://groups.google.com/d/topic/elasticsearch/Rd3Bg9qeyws/unsubscribe](https://groups.google.com/d/topic/elasticsearch/Rd3Bg9qeyws/unsubscribe). > To unsubscribe from this group and all its topics, send an email to > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoDsr0q-bUkHb%3DkxH%2BhRYKZ2m5gspRZqeSX1KDe%2B\_dTViQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoDsr0q-bUkHb%3DkxH%2BhRYKZ2m5gspRZqeSX1KDe%2B_dTViQ%40mail.gmail.com) > . > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CALU-2jwv%3DVuuSM36Rt-nfSv8x%2BjLvMn4nHbGoPBuDHjWgMX3ng%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CALU-2jwv%3DVuuSM36Rt-nfSv8x%2BjLvMn4nHbGoPBuDHjWgMX3ng%40mail.gmail.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Bijuv\_V](https://avatars.discourse-cdn.com/v4/letter/b/b77776/32.png) [@Bijuv\_V](https://discuss.elastic.co/u/Bijuv_V)\ **Post date:** [December 11, 2013, 10:56pm UTC](https://discuss.elastic.co/t/interval-based-search-in-elastic-search/14798/4 "2013-12-11T22:56:20Z")
I got it working - overlooked the term match {"query":{ "filtered": { "query": { "match\_all": {} }, "filter": { "nested": { "path": "availablities", "filter": { "bool": { "must": [ { "range": { "availablities.starttime":{"lte": "2013-12-10T11:00:00"} } }, { "range": { "availablities.endtime": { "gte": "2013-12-10T11:35:00" } } } ] } } } } } } } On Wed, Dec 11, 2013 at 11:41 PM, Bijuv V [vvbiju2005@gmail.com](mailto:vvbiju2005@gmail.com) wrote: > Hi Matt > > I did the below steps and the search doesnt return the correct data > > HTTP PUT Request : [http://localhost:9200/avails/avail/\_mapping](http://localhost:9200/avails/avail/_mapping) > > { > "avail" : > { > "properties" : > { > "id": {type:"long"}, > "location" : {"type" : "string"}, > "availablities" : > { > "type" : "nested", > "properties": > { > > ``` > "endtime":{"type":"date", "format" : > > ``` > > "date\_hour\_minute\_second"}, > "starttime":{"type":"date" , "format" : > "date\_hour\_minute\_second"} > > ``` > } > > } > > } > } > > ``` > > } > > Inserted 2 records > > { > avail: > { > "id": 1, > "location" : "us", > "availablities": > [ > { > "starttime": "2013-12-10T10:00:00" , > "endtime": "2013-12-10T13:00:00" > }, > { > "starttime": "2013-12-10T14:00:00" , > "endtime": "2013-12-10T15:30:00" > }, > { > "starttime": "2013-12-10T16:00:00" , > "endtime": "2013-12-10T18:00:00" > } > ] > } > } > > { > avail: > { > "id": 2, > "location" : "uk", > "availablities": > [ > { > "starttime": "2013-12-10T10:00:00" , > "endtime": "2013-12-10T11:00:00" > }, > { > "starttime": "2013-12-10T11:05:00" , > "endtime": "2013-12-10T11:30:00" > }, > { > "starttime": "2013-12-10T17:00:00" , > "endtime": "2013-12-10T18:00:00" > } > ] > } > } > > Then performed the search > > [http://localhost:9200/avails/\_search](http://localhost:9200/avails/_search) > > {"query":{ > "filtered": { > "query": { > "match\_all": {} > }, > "filter": { > "nested": { > "path": "availablities", > "filter": { > "bool": { > "must": [ > { > "term": { > "availablities.starttime": > "2013-12-10T11:00:00/m" > } > }, > { > "range": { > "availablities.endtime": { > "gte": "2013-12-10T11:00:00||+35m/m" > } > } > } > ] > } > } > } > } > } > } > } > > Now the issues - > > a. The starttime is not 11:00 AM in my data but its 10:00AM. So how does > the equality match of starttime to 11:00AM match? > > b. when I tried the same, it complains that date is malformed at /m. > > Regards > > On Tue, Dec 10, 2013 at 9:15 PM, Matt Weber [matt.weber@gmail.com](mailto:matt.weber@gmail.com) wrote: > > > You will have to use a nested mapping [1] for the Availabilities field. > > Then you can do a nested filter [2] similar to the following: > > > > "constant\_score" : { > > "filter" : { > > "nested": { > > "path": "Availabilities", > > "filter": { > > "bool": { > > "must": [ > > {"term": {"Availabilities.starttime": "10/12/2013 > > 11:00:00/m"}}, > > {"range": {"Availabilities.endtime": > > {"gte":"10/12/2013 11:00:00||+35m/m"}}} > > ] > > } > > } > > } > > } > > } > > > > Hope this helps. > > > > Thanks, > > Matt Weber > > > > [1] > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html) > > [2] > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/query-dsl-nested-filter.html) > > > > On Tue, Dec 10, 2013 at 11:37 AM, Bijuv V [vvbiju2005@gmail.com](mailto:vvbiju2005@gmail.com) wrote: > > > > > I have a search requirement , which Im trying to solve using Elastic > > > Search. > > > > > > Assuming that the below JSON is indexed, what will be the query to fetch > > > an availability with starttime as 10/12/2013 11:00:00 and time period of 35 > > > minutes . The search should return only the MeetingRoom1. > > > > > > Im sure that I should be looking at date functions but not able to find > > > a correct query criteria that can be used . Any pointers? > > > > > > Meeting Room 1: > > > > > > { > > > "meetingroomid": 45, > > > "Availablities": > > > [ > > > { > > > "starttime": "10/12/2013 10:00:00" , > > > "endtime": "10/12/2013 13:00:00" > > > }, > > > { > > > "starttime": "10/12/2013 14:00:00" , > > > "endtime": "10/12/2013 15:30:00" > > > }, > > > { > > > "starttime": "10/12/2013 16:00:00" , > > > "endtime": "10/12/2013 18:00:00" > > > } > > > ] > > > } > > > > > > Meeting Room 2: > > > > > > { > > > "meetingroomid": 100, > > > "Availablities": > > > [ > > > { > > > "starttime": "10/12/2013 10:00:00" , > > > "endtime": "10/12/2013 11:01:00" > > > }, > > > { > > > "starttime": "10/12/2013 11:05:00" , > > > "endtime": "10/12/2013 12:30:00" > > > }, > > > { > > > "starttime": "10/12/2013 16:00:00" , > > > "endtime": "10/12/2013 18:00:00" > > > } > > > ] > > > } > > > > > > -- > > > You received this message because you are subscribed to the Google > > > Groups "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send > > > an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > > > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com) > > > . > > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > > > -- > > You received this message because you are subscribed to a topic in the > > Google Groups "elasticsearch" group. > > To unsubscribe from this topic, visit > > [https://groups.google.com/d/topic/elasticsearch/Rd3Bg9qeyws/unsubscribe](https://groups.google.com/d/topic/elasticsearch/Rd3Bg9qeyws/unsubscribe). > > To unsubscribe from this group and all its topics, send an email to > > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoDsr0q-bUkHb%3DkxH%2BhRYKZ2m5gspRZqeSX1KDe%2B\_dTViQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoDsr0q-bUkHb%3DkxH%2BhRYKZ2m5gspRZqeSX1KDe%2B_dTViQ%40mail.gmail.com) > > . > > > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CALU-2jyRXUfqQFqFTA%2BLqAx3xqC24ry406x3E7-UBpjvdq9RNg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CALU-2jyRXUfqQFqFTA%2BLqAx3xqC24ry406x3E7-UBpjvdq9RNg%40mail.gmail.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![mattweber](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mattweber/32/44940_2.png) [@mattweber](https://discuss.elastic.co/u/mattweber)\ **Post date:** [December 11, 2013, 11:10pm UTC](https://discuss.elastic.co/t/interval-based-search-in-elastic-search/14798/5 "2013-12-11T23:10:38Z")
Glad this worked... I see your example doesn't use the date math which means you must be calculating the endtime yourself. If you wanted elasticsearch to calculate the correct endtime based on a given amount of time you can use the date math [1] functionality like I do below: curl -XPOST '[http://localhost:9200/avails/\_search](http://localhost:9200/avails/_search)' -d '{ "query": { "constant\_score" : { "filter" : { "nested": { "path": "availablities", "filter": { "bool": { "must": [ {"range": {"availablities.starttime": {"lte":"2013-12-10T11:00:00||/m"}}}, {"range": {"availablities.endtime": {"gte":"2013-12-10T11:00:00||+35m/m"}}} ] } } } } } } }' Thanks, Matt Weber [1] > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. On Wed, Dec 11, 2013 at 2:56 PM, Bijuv V [vvbiju2005@gmail.com](mailto:vvbiju2005@gmail.com) wrote: > I got it working - overlooked the term match > > {"query":{ > "filtered": { > "query": { > "match\_all": {} > }, > "filter": { > "nested": { > "path": "availablities", > "filter": { > "bool": { > "must": [ > { > "range": { > "availablities.starttime":{"lte": > "2013-12-10T11:00:00"} > } > }, > { > "range": { > "availablities.endtime": { > "gte": "2013-12-10T11:35:00" > } > } > } > ] > } > } > } > } > } > } > } > > On Wed, Dec 11, 2013 at 11:41 PM, Bijuv V [vvbiju2005@gmail.com](mailto:vvbiju2005@gmail.com) wrote: > > > Hi Matt > > > > I did the below steps and the search doesnt return the correct data > > > > HTTP PUT Request : [http://localhost:9200/avails/avail/\_mapping](http://localhost:9200/avails/avail/_mapping) > > > > { > > "avail" : > > { > > "properties" : > > { > > "id": {type:"long"}, > > "location" : {"type" : "string"}, > > "availablities" : > > { > > "type" : "nested", > > "properties": > > { > > > > ``` > > "endtime":{"type":"date", "format" : > > > > ``` > > > > "date\_hour\_minute\_second"}, > > "starttime":{"type":"date" , "format" : > > "date\_hour\_minute\_second"} > > > > ``` > > } > > > > } > > > > } > > } > > > > ``` > > > > } > > > > Inserted 2 records > > > > { > > avail: > > { > > "id": 1, > > "location" : "us", > > "availablities": > > [ > > { > > "starttime": "2013-12-10T10:00:00" , > > "endtime": "2013-12-10T13:00:00" > > }, > > { > > "starttime": "2013-12-10T14:00:00" , > > "endtime": "2013-12-10T15:30:00" > > }, > > { > > "starttime": "2013-12-10T16:00:00" , > > "endtime": "2013-12-10T18:00:00" > > } > > ] > > } > > } > > > > { > > avail: > > { > > "id": 2, > > "location" : "uk", > > "availablities": > > [ > > { > > "starttime": "2013-12-10T10:00:00" , > > "endtime": "2013-12-10T11:00:00" > > }, > > { > > "starttime": "2013-12-10T11:05:00" , > > "endtime": "2013-12-10T11:30:00" > > }, > > { > > "starttime": "2013-12-10T17:00:00" , > > "endtime": "2013-12-10T18:00:00" > > } > > ] > > } > > } > > > > Then performed the search > > > > [http://localhost:9200/avails/\_search](http://localhost:9200/avails/_search) > > > > {"query":{ > > "filtered": { > > "query": { > > "match\_all": {} > > }, > > "filter": { > > "nested": { > > "path": "availablities", > > "filter": { > > "bool": { > > "must": [ > > { > > "term": { > > "availablities.starttime": > > "2013-12-10T11:00:00/m" > > } > > }, > > { > > "range": { > > "availablities.endtime": { > > "gte": "2013-12-10T11:00:00||+35m/m" > > } > > } > > } > > ] > > } > > } > > } > > } > > } > > } > > } > > > > Now the issues - > > > > a. The starttime is not 11:00 AM in my data but its 10:00AM. So how does > > the equality match of starttime to 11:00AM match? > > > > b. when I tried the same, it complains that date is malformed at /m. > > > > Regards > > > > On Tue, Dec 10, 2013 at 9:15 PM, Matt Weber [matt.weber@gmail.com](mailto:matt.weber@gmail.com) wrote: > > > > > You will have to use a nested mapping [1] for the Availabilities field. > > > Then you can do a nested filter [2] similar to the following: > > > > > > "constant\_score" : { > > > "filter" : { > > > "nested": { > > > "path": "Availabilities", > > > "filter": { > > > "bool": { > > > "must": [ > > > {"term": {"Availabilities.starttime": > > > "10/12/2013 11:00:00/m"}}, > > > {"range": {"Availabilities.endtime": > > > {"gte":"10/12/2013 11:00:00||+35m/m"}}} > > > ] > > > } > > > } > > > } > > > } > > > } > > > > > > Hope this helps. > > > > > > Thanks, > > > Matt Weber > > > > > > [1] > > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html) > > > [2] > > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/query-dsl-nested-filter.html) > > > > > > On Tue, Dec 10, 2013 at 11:37 AM, Bijuv V [vvbiju2005@gmail.com](mailto:vvbiju2005@gmail.com) wrote: > > > > > > > I have a search requirement , which Im trying to solve using Elastic > > > > Search. > > > > > > > > Assuming that the below JSON is indexed, what will be the query to > > > > fetch an availability with starttime as 10/12/2013 11:00:00 and time period > > > > of 35 minutes . The search should return only the MeetingRoom1. > > > > > > > > Im sure that I should be looking at date functions but not able to find > > > > a correct query criteria that can be used . Any pointers? > > > > > > > > Meeting Room 1: > > > > > > > > { > > > > "meetingroomid": 45, > > > > "Availablities": > > > > [ > > > > { > > > > "starttime": "10/12/2013 10:00:00" , > > > > "endtime": "10/12/2013 13:00:00" > > > > }, > > > > { > > > > "starttime": "10/12/2013 14:00:00" , > > > > "endtime": "10/12/2013 15:30:00" > > > > }, > > > > { > > > > "starttime": "10/12/2013 16:00:00" , > > > > "endtime": "10/12/2013 18:00:00" > > > > } > > > > ] > > > > } > > > > > > > > Meeting Room 2: > > > > > > > > { > > > > "meetingroomid": 100, > > > > "Availablities": > > > > [ > > > > { > > > > "starttime": "10/12/2013 10:00:00" , > > > > "endtime": "10/12/2013 11:01:00" > > > > }, > > > > { > > > > "starttime": "10/12/2013 11:05:00" , > > > > "endtime": "10/12/2013 12:30:00" > > > > }, > > > > { > > > > "starttime": "10/12/2013 16:00:00" , > > > > "endtime": "10/12/2013 18:00:00" > > > > } > > > > ] > > > > } > > > > > > > > -- > > > > You received this message because you are subscribed to the Google > > > > Groups "elasticsearch" group. > > > > To unsubscribe from this group and stop receiving emails from it, send > > > > an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > > > > > > > To view this discussion on the web visit > > > > [https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com) > > > > . > > > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > > > > > -- > > > You received this message because you are subscribed to a topic in the > > > Google Groups "elasticsearch" group. > > > To unsubscribe from this topic, visit > > > [https://groups.google.com/d/topic/elasticsearch/Rd3Bg9qeyws/unsubscribe](https://groups.google.com/d/topic/elasticsearch/Rd3Bg9qeyws/unsubscribe). > > > To unsubscribe from this group and all its topics, send an email to > > > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoDsr0q-bUkHb%3DkxH%2BhRYKZ2m5gspRZqeSX1KDe%2B\_dTViQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoDsr0q-bUkHb%3DkxH%2BhRYKZ2m5gspRZqeSX1KDe%2B_dTViQ%40mail.gmail.com) > > > . > > > > > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/CALU-2jyRXUfqQFqFTA%2BLqAx3xqC24ry406x3E7-UBpjvdq9RNg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CALU-2jyRXUfqQFqFTA%2BLqAx3xqC24ry406x3E7-UBpjvdq9RNg%40mail.gmail.com) > . > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoBkJ457mWJh%3D4ppEs5oLRST1gDfCiv1oa55Mg\_d-rbC8w%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoBkJ457mWJh%3D4ppEs5oLRST1gDfCiv1oa55Mg_d-rbC8w%40mail.gmail.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 2:01am UTC](https://discuss.elastic.co/t/interval-based-search-in-elastic-search/14798/6 "2017-07-06T02:01:53Z")