Date range query not working on elastic 2.4 cluster

Question

Elasticsearch query with date range query isn't working.

{
  "from": 0,
  "size": 10,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "100",
            "fields": [
              "nodeId"
            ],
            "default_operator": "and"
          }
        },
        {
          "query_string": {
            "query": "DAILY",
            "fields": [
              "aggLevel"
            ],
            "default_operator": "and"
          }
        },
        {
          "query_string": {
            "query": "23",
            "fields": [
              "replId"
            ],
            "default_operator": "and"
          }
        },
        {
          "range": {
            "sales.date": {
              "gte": "01-02-2020",
              "lte": "08-03-2020"
            }
          }
        }
      ]
    }
  }
}

Answer 1

You are using the wrong date format, the correct one is yyyy-mm-dd while you are using dd-mm-yyyy. Please refer date data type and date range query ES doc for more information.

Correct example

Index mapping

{
    "mappings": {
        "my_type": {
            "properties": {
                "sales_date": {
                    "type": "date"
                }
            }
        }
    }
}

Index sample docs

{
   "sales_date" :  "01-02-2020"
}

{
   "sales_date" :  "2020-02-05"
}

{
   "sales_date" :  "2020-03-08"
}
{
   "sales_date" :  "2020-03-15"
}

Search query with date range query

{
    "query": {
        "range" : {
            "sales_date" : {
                "gte" : "2020-02-01", --> NOTE DIFFERENCE
                "lte" : "2020-03-01"
            }
        }
    }
}

Search results

"hits": [
         {
            "_index": "so-60584496",
            "_type": "my_type",
            "_id": "1",
            "_score": 1.0,
            "_source": {
               "sales_date": "2020-02-01"
            }
         },
         {
            "_index": "so-60584496",
            "_type": "my_type",
            "_id": "2",
            "_score": 1.0,
            "_source": {
               "sales_date": "2020-02-05"
            }
         }
      ]