Date range is not working as expected

Elastic Stack Elasticsearch
1

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

2

You can try this:

"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.

3

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

4

so sad...

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