Elasticsearch查询需求：判断expiryDate加30天后是否小于当前日期

Got it, let's break down how to solve this problem. The goal is to take the expiryDate field, add 30 days to it, and check if that new date is earlier than the current date. Here's what you need to do:

1. Ensure Correct Field Mapping First #

First, make sure your expiryDate field is mapped as a date type with the right format (since your date uses the dd/MM/yyyy pattern). If you haven't set this up yet, here's an example mapping for your index:

PUT /your_index
{
  "mappings": {
    "properties": {
      "_id": {"type": "keyword"},
      "name": {"type": "text"},
      "expiryDate": {"type": "date", "format": "dd/MM/yyyy"}
    }
  }
}

Using a date type is crucial because it lets Elasticsearch natively handle date arithmetic instead of parsing text strings (which is slower and error-prone).

2. Write the Query with Painless Scripting #

We'll use a script query with Elasticsearch's Painless language to perform the date calculation and comparison. Here's the complete search query:

GET /your_index/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "script": {
            "script": {
              "source": "doc['expiryDate'].value.plusDays(30).isBefore(ZonedDateTime.now())",
              "lang": "painless"
            }
          }
        }
      ]
    }
  }
}

Let's break down the script logic: #

• doc['expiryDate'].value: Retrieves the date value from the document (since we mapped it as a date type, this is a native date object).
• .plusDays(30): Adds 30 days to the expiry date.
• .isBefore(ZonedDateTime.now()): Checks if the adjusted date is earlier than the current date (uses the server's default timezone; if you need UTC, replace with ZonedDateTime.now(ZoneOffset.UTC)).

3. Fallback if expiryDate is a Text Field (Not Recommended) #

If for some reason you can't change the mapping and expiryDate is stored as text, you'll need to parse it into a date first in the script:

GET /your_index/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "script": {
            "script": {
              "source": "ZonedDateTime.parse(doc['expiryDate'].value, DateTimeFormatter.ofPattern('dd/MM/yyyy')).plusDays(30).isBefore(ZonedDateTime.now())",
              "lang": "painless"
            }
          }
        }
      ]
    }
  }
}

Again, this is less efficient than using a date type mapping, so fixing the mapping is the better long-term solution.

内容的提问来源于stack exchange，提问作者Muthukumar Marichamy