Elasticsearch - Trouble querying for exact date with range query

Question

I have the following mapping definition in my events index:

{
  "events": {
    "mappings": {
      "properties": {
        "data": {
          "properties": {
            "reportDate": {
              "type": "date",
              "format": "M/d/YYYY"
            }
         }
       }
    }
  }
}

And an example doc:

{
    "_index": "events",
    "_type": "_doc",
    "_id": "12345",
    "_version": 1,
    "_seq_no": 90,
    "_primary_term": 1,
    "found": true,
    "_source": {
        "data": {
            "reportDate": "12/4/2018",
        }
    }
}

My goal is query for docs with an exact data.reportDate of 12/4/2018, but when I run this query:

{
    "query": {
        "range": {
            "data.reportDate": {
                "lte": "12/4/2018",
                "gte": "12/4/2018",
                "format": "M/d/YYYY"
            }
        }
    }
}

I instead get all of the docs that have a data.reportDate that is in the year 2018, not just 12/4/2018. I've tried setting relation to CONTAINS and WITHIN with no luck. Any ideas?

Answer 1

You need to change your date format from M/d/YYYY to M/d/yyyy. Refer to this ES official documentation to know more about date formats. You can even refer to this documentation to know about the difference between yyyy and YYYY

yyyy specifies the calendar year whereas YYYY specifies the year (of “Week of Year”)

Adding a working example with index mapping, data, search query, and search result

Index Mapping:

{
  "mappings": {
    "properties": {
      "data": {
        "properties": {
          "reportDate": {
            "type": "date",
            "format": "M/d/yyyy"
          }
        }
      }
    }
  }
}

Index Data:

{
  "data": {
    "reportDate": "12/3/2018"
  }
}
{
  "data": {
    "reportDate": "12/4/2018"
  }
}
{
  "data": {
    "reportDate": "12/5/2018"
  }
}

Search Query:

{
  "query": {
    "bool": {
      "must": {
        "range": {
          "data.reportDate": {
            "lte": "12/4/2018",
            "gte": "12/4/2018"
          }
        }
      }
    }
  }
}

Search Result:

"hits": [
      {
        "_index": "65312594",
        "_type": "_doc",
        "_id": "1",
        "_score": 1.0,
        "_source": {
          "data": {
            "reportDate": "12/4/2018"
          }
        }
      }
    ]