# Date range is not working as expected

**URL:** <https://discuss.elastic.co/t/date-range-is-not-working-as-expected/106243>\
**Category:** Elasticsearch\
**Created:** [November 2, 2017, 7:40pm UTC](https://discuss.elastic.co/t/date-range-is-not-working-as-expected/106243 "2017-11-02T19:40:34Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![sa\_shashank](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@sa\_shashank](https://discuss.elastic.co/u/sa_shashank)\
**Post date:** [November 2, 2017, 7:40pm UTC](https://discuss.elastic.co/t/date-range-is-not-working-as-expected/106243/1 "2017-11-02T19:40:34Z")

</div>

I am trying to build a range query on date field.

```
POST /test_index/test_record/_search
{
    "size": 5000,
	"query": {
		"bool": {
			"must": [],
			"filter": [{
				"range": {
					"insertDate": {
						"gte": "2017-11-02 14:26:59",
						"lte": "2017-11-02 14:28:59"
					}
				}
			}]
		}
	}
}

```

Indexed field mapping:

```
      "insertDate": {
				"type": "date",
				"format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis",
				"ignore_malformed": true
			},

```

It doesn't work if i use aforementioned format. But if I try following  
"filter" : [{  
"range" : {  
"insertDate" : {  
"gte" : "10/24/2017",  
"lte" : "10/01/2017"  
}  
}  
}  
]

It works.

Could you please help me to identify the issue in my query or index mapping?

Thanks,  
SA

---

<div class="post-metadata">

**Author:** ![Canappeco](https://avatars.discourse-cdn.com/v4/letter/c/5fc32e/32.png) [@Canappeco](https://discuss.elastic.co/u/Canappeco)\
**Post date:** [November 3, 2017, 10:41am UTC](https://discuss.elastic.co/t/date-range-is-not-working-as-expected/106243/2 "2017-11-03T10:41:20Z")

</div>

You can try this:

```auto
"filter": [{
    "range": {
        "insertDate": {
            "gte": "2017-11-02 14:26:59",
            "lte": "2017-11-02 14:28:59",
            "format": "yyyy-MM-dd HH:mm:ss"
        }
    }
}]

```

Hope it works.

---

<div class="post-metadata">

**Author:** ![sa\_shashank](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@sa\_shashank](https://discuss.elastic.co/u/sa_shashank)\
**Post date:** [November 3, 2017, 3:05pm UTC](https://discuss.elastic.co/t/date-range-is-not-working-as-expected/106243/3 "2017-11-03T15:05:07Z")

</div>

Thanks Ming. I have tried this way also, but it didn't work.

---

<div class="post-metadata">

**Author:** ![Canappeco](https://avatars.discourse-cdn.com/v4/letter/c/5fc32e/32.png) [@Canappeco](https://discuss.elastic.co/u/Canappeco)\
**Post date:** [November 3, 2017, 4:52pm UTC](https://discuss.elastic.co/t/date-range-is-not-working-as-expected/106243/4 "2017-11-03T16:52:19Z")

</div>

so sad...

But can you show your query as well as response? Maybe we can make it easy.

---

<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:** [December 1, 2017, 4:52pm UTC](https://discuss.elastic.co/t/date-range-is-not-working-as-expected/106243/5 "2017-12-01T16:52:28Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.