Date Ranges in Elastic Search 8.1

Question

I have indexed data in the below format.

{
  "_index": "product",
  "_id": "1",
  "_score": 1.0,
  "_source": {
    "product_id": "P01",
    "product_type": "P1",
    "updated_date": "2022-12-21-12-55-58"
  }
}

I need to filter documents based on a date range.

I have written the following query for ranges.

{
  "query": {
    "bool": {
      "filter": [
        { "match": { "product_type": "P1" } },
        {
          "range": {
            "updated_date": {
              "gte": "2022-12-25-12-55-58",
              "lte": "2022-12-30-12-55-58"
            }
          }
        }
      ]
    }
  }
}

I am not getting proper output, ideally, it should not allow date ranges before 2022-12-25-12-55-58, but I am getting all of them.

Please suggest to me what improvement can be done in order to filter date ranges. (Time Stamps).

Thanks in advance.

Answer 1

Given your dates don't have the standard ISO8601 format, I think they have been indexed as text instead and hence range queries don't work the way you expect, i.e. they filter in a lexicographical way instead of chronological.

You have a few options to fix this:

A. you can reindex your data with a proper date format

B. you can reindex your data with the same format, but you can first modify your mapping to this:

"updated_date": {
    "type": "date",
    "format": "yyyy-MM-dd-HH-mm-ss"
}

C. You don't want to reindex all your data, but you are allowed to add a date sub-field to your existing updated_date with the proper format field and then you can simply update your index:

PUT yourindex/_mapping
{
  "properties": {
    "updated_date": {
        "type": "text",
        "fields": {
           "date": {
              "type": "date",
              "format": "yyyy-MM-dd-HH-mm-ss"
           }
        }
    }
  }
}

Then run this to update all your documents:

POST yourindex/_update_by_query?wait_for_completion=false

When this task is done, you'll be able to run your query on the new sub-field

      "range": {
        "updated_date.date": {
          "gte": "2022-12-25-12-55-58",
          "lte": "2022-12-30-12-55-58",
          "format": "yyyy-MM-dd-HH-mm-ss"
        }
      }