# Getting date query to work

**URL:** <https://discuss.elastic.co/t/getting-date-query-to-work/8386>\
**Category:** Elasticsearch\
**Created:** [July 11, 2012, 4:44pm UTC](https://discuss.elastic.co/t/getting-date-query-to-work/8386 "2012-07-11T16:44:26Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![coys](https://avatars.discourse-cdn.com/v4/letter/c/eb8c5e/32.png) [@coys](https://discuss.elastic.co/u/coys)\
**Post date:** [July 11, 2012, 4:44pm UTC](https://discuss.elastic.co/t/getting-date-query-to-work/8386/1 "2012-07-11T16:44:26Z")

</div>

Hello, I have dates which are in the MM/dd/YYYY format and I indexed  
them as  
curl -XPUT '[http://localhost:9200/collectionName/startDate/\_mapping](http://localhost:9200/collectionName/startDate/_mapping)' -  
d '{  
"indexName": {  
"properties" : {  
"calendar.startDate" : {"type":"date", "format":"MM/dd/YYYY" }  
}  
}  
}  
'curl -XPUT '[http://localhost:9200/collectionName/endDate/\_mapping](http://localhost:9200/collectionName/endDate/_mapping)' -d  
'{  
"indexName": {  
"properties" : {  
"calendar.endDate" : {"type":"date", "format":"MM/dd/YYYY" }  
}  
}  
}  
'  
Can I index a date in the MM/dd/YYYY format as described and I am  
doing it the correct way? Or should I store it as YYYY-mm-dd?

Also, my query looks like this  
{"must":  
[  
{ "bool":  
{  
"must": [ {  
"range": {  
"calendar.startDate": { "gte":  
"7/11/2012" }  
}  
},  
{  
"range": {  
"calendar.endDate": { "lte":  
"8/11/2012" }  
}  
}  
]  
}  
}  
]  
}  
Right now I have this query but its not returning anything. Thanks!

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [July 11, 2012, 6:43pm UTC](https://discuss.elastic.co/t/getting-date-query-to-work/8386/2 "2012-07-11T18:43:29Z")

</div>

If I understand you correctly, you are indexing records with type  
startDate, that contain fields calendar.startDate, then you are indexing  
records with type endDate, that contain fields calendar.endDate, and then  
you are issuing a query for a record that contains a startDate field  
greater than or equal to 7/11/2012 and an endData field less than or equal  
to 8/11/2012 _at the same time_. According to your mapping such record  
cannot exist. So, I don't think I quite understand what you are trying  
to achieve here. Could you give an example of your data and expected  
results?

On Wednesday, July 11, 2012 12:44:26 PM UTC-4, coys wrote:

> Hello, I have dates which are in the MM/dd/YYYY format and I indexed  
> them as  
> curl -XPUT '[http://localhost:9200/collectionName/startDate/\_mapping](http://localhost:9200/collectionName/startDate/_mapping)' -  
> d '{  
> "indexName": {  
> "properties" : {  
> "calendar.startDate" : {"type":"date", "format":"MM/dd/YYYY" }  
> }  
> }  
> }  
> 'curl -XPUT '[http://localhost:9200/collectionName/endDate/\_mapping](http://localhost:9200/collectionName/endDate/_mapping)' -d  
> '{  
> "indexName": {  
> "properties" : {  
> "calendar.endDate" : {"type":"date", "format":"MM/dd/YYYY" }  
> }  
> }  
> }  
> '  
> Can I index a date in the MM/dd/YYYY format as described and I am  
> doing it the correct way? Or should I store it as YYYY-mm-dd?
> 
> Also, my query looks like this  
> {"must":  
> [  
> { "bool":  
> {  
> "must": [ {  
> "range": {  
> "calendar.startDate": { "gte":  
> "7/11/2012" }  
> }  
> },  
> {  
> "range": {  
> "calendar.endDate": { "lte":  
> "8/11/2012" }  
> }  
> }  
> ]  
> }  
> }  
> ]  
> }  
> Right now I have this query but its not returning anything. Thanks!

---

<div class="post-metadata">

**Author:** ![coys](https://avatars.discourse-cdn.com/v4/letter/c/eb8c5e/32.png) [@coys](https://discuss.elastic.co/u/coys)\
**Post date:** [July 11, 2012, 8:45pm UTC](https://discuss.elastic.co/t/getting-date-query-to-work/8386/3 "2012-07-11T20:45:16Z")

</div>

Hi Igor, every document that I create in the collection has a start  
date and end date. I want to find a list of documents currently valid  
-- eg valid documents say \> startDate and \< endDate  
documents valid from 7/11/2012 to 8/11/2012  
This was my initial mapping,  
"calendar" : {  
"dynamic" : "true",  
"properties" : {  
"endDate" : {  
"type" : "string"  
},  
"startDate" : {  
"type" : "string"  
}  
}  
}  
and to be able to search on date field I created these 2 mappings,  
names of mappings startDate and endDate on calendar.startDate and  
calendar.endDate  
"startDate" : {  
"properties" : {  
"calendar.startDate" : {  
"type" : "date",  
"format" : "MM/dd/yyyy"  
}  
}  
},  
"endDate" : {  
"properties" : {  
"calendar.endDate" : {  
"type" : "date",  
"format" : "MM/dd/yyyy"  
}  
}  
}

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [July 11, 2012, 9:00pm UTC](https://discuss.elastic.co/t/getting-date-query-to-work/8386/4 "2012-07-11T21:00:57Z")

</div>

Something like this [dateranges.sh · GitHub](https://gist.github.com/9d67c5c2b047a962dcf5) ?

On Wednesday, July 11, 2012 4:45:16 PM UTC-4, coys wrote:

> Hi Igor, every document that I create in the collection has a start  
> date and end date. I want to find a list of documents currently valid  
> -- eg valid documents say \> startDate and \< endDate  
> documents valid from 7/11/2012 to 8/11/2012  
> This was my initial mapping,  
> "calendar" : {  
> "dynamic" : "true",  
> "properties" : {  
> "endDate" : {  
> "type" : "string"  
> },  
> "startDate" : {  
> "type" : "string"  
> }  
> }  
> }  
> and to be able to search on date field I created these 2 mappings,  
> names of mappings startDate and endDate on calendar.startDate and  
> calendar.endDate  
> "startDate" : {  
> "properties" : {  
> "calendar.startDate" : {  
> "type" : "date",  
> "format" : "MM/dd/yyyy"  
> }  
> }  
> },  
> "endDate" : {  
> "properties" : {  
> "calendar.endDate" : {  
> "type" : "date",  
> "format" : "MM/dd/yyyy"  
> }  
> }  
> }

---

<div class="post-metadata">

**Author:** ![coys](https://avatars.discourse-cdn.com/v4/letter/c/eb8c5e/32.png) [@coys](https://discuss.elastic.co/u/coys)\
**Post date:** [July 11, 2012, 10:22pm UTC](https://discuss.elastic.co/t/getting-date-query-to-work/8386/5 "2012-07-11T22:22:02Z")

</div>

Hi Igor, thanks a lot for the example. However, when I try to re-map  
the field as a date its giving me a mergemapper exception. I tried,  
curl -XPUT '[http://localhost:9200/plansCollection/plans/\_mapping](http://localhost:9200/plansCollection/plans/_mapping)' -d  
'{  
"plans": {  
"properties" : {  
"calendar" : {  
"dynamic" : "true",  
"properties" :{  
"startDate" : {"type":"date", "format":"MM/dd/yyyy" },  
"endDate" : {"type":"date", "format":"MM/dd/yyyy" }  
}  
}  
}  
}  
}  
'  
where index name = plansCollection and index type is plans  
It gave me {"error":"MergeMappingException[Merge failed with failures  
{[mapper [calendar.startDate] of different type, current\_type  
[string], merged\_type [date], mapper [calendar.endDate] of different  
type, current\_type [string], merged\_type [date]]}]"  
Is there a way I can fix the mapping now so that the above query would  
work?

I also tried /plansCollection/planA/ .. but the date query would not  
work with that. thanks!

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [July 11, 2012, 10:24pm UTC](https://discuss.elastic.co/t/getting-date-query-to-work/8386/6 "2012-07-11T22:24:09Z")

</div>

You cannot change type of a field once it's created. You will have to  
delete the index or at least the mapping (type) that you need to change and  
reindex your data.

On Wednesday, July 11, 2012 6:22:02 PM UTC-4, coys wrote:

> Hi Igor, thanks a lot for the example. However, when I try to re-map  
> the field as a date its giving me a mergemapper exception. I tried,  
> curl -XPUT '[http://localhost:9200/plansCollection/plans/\_mapping](http://localhost:9200/plansCollection/plans/_mapping)' -d  
> '{  
> "plans": {  
> "properties" : {  
> "calendar" : {  
> "dynamic" : "true",  
> "properties" :{  
> "startDate" : {"type":"date", "format":"MM/dd/yyyy" },  
> "endDate" : {"type":"date", "format":"MM/dd/yyyy" }  
> }  
> }  
> }  
> }  
> }  
> '  
> where index name = plansCollection and index type is plans  
> It gave me {"error":"MergeMappingException[Merge failed with failures  
> {[mapper [calendar.startDate] of different type, current\_type  
> [string], merged\_type [date], mapper [calendar.endDate] of different  
> type, current\_type [string], merged\_type [date]]}]"  
> Is there a way I can fix the mapping now so that the above query would  
> work?
> 
> I also tried /plansCollection/planA/ .. but the date query would not  
> work with that. thanks!

---

<div class="post-metadata">

**Author:** ![coys](https://avatars.discourse-cdn.com/v4/letter/c/eb8c5e/32.png) [@coys](https://discuss.elastic.co/u/coys)\
**Post date:** [July 11, 2012, 10:38pm UTC](https://discuss.elastic.co/t/getting-date-query-to-work/8386/7 "2012-07-11T22:38:16Z")

</div>

Thanks a lot!

---

<div class="post-metadata">

**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, 3:20am UTC](https://discuss.elastic.co/t/getting-date-query-to-work/8386/8 "2017-07-06T03:20:33Z")

</div>