How can I make query between dates in ElasticSearch?

Question

I am trying to filter between two dates in ElasticSearch but I have the next error

This is the query

{
    "query": {
        "bool": {
            "must": [
                {
                    "match": {
                        "message": "start_time"
                    }
                },
                {
                    "range": {
                        "start_time": {
                            "gte": "09-02-2016",
                            "lte": "09-02-2016"
                        }
                    }
                }
            ]
        }
    }
}

and the error

{
      "error": {
               "root_cause": [
              {
                "type": "query_shard_exception",
                "reason": "failed to create query: For input string: \"09-02-2016\"",
                "index_uuid": "nLn_JFMDShW_PxAf7vgFyg",
                "index": "accidents"
              }
            ],
            "type": "search_phase_execution_exception",
            "reason": "all shards failed",
            "phase": "query",
            "grouped": true,
            "failed_shards": [
              {
                "shard": 0,
                "index": "accidents",
                "node": "x5unJW2PQsquLIpFsi9YQA",
                "reason": {
                  "type": "query_shard_exception",
                  "reason": "failed to create query: For input string: \"09-02-2016\"",
                  "index_uuid": "nLn_JFMDShW_PxAf7vgFyg",
                  "index": "accidents",
                  "caused_by": {
                    "type": "number_format_exception",
                    "reason": "For input string: \"09-02-2016\""
                  }
                }
              }
            ]
          },
          "status": 400
        }

This is the field to filter

And It's an example that how start_time is saved

I had understood that ElasticSearch converts from date to number to have better performance but maybe it's wrong

I would like to be sure about if I am making the query well or why I've this problem

can you please post the actual code you are using and not a screenshot, it's very hard to read and replicate otherwise — warkolm
– warkolm, Commented Nov 1, 2022 at 1:32

Paulo · Accepted Answer · 2022-11-01 06:42:12Z

If you have configures the mapping correctly, you probably can use the format option to specify the date format. So the query looks something like the one below.

{
    "query": {
        "bool": {
            "must": [
                {
                    "match": {
                        "message": "start_time"
                    }
                },
                {
                    "range": {
                        "start_time": {
                            "gte": "09-02-2016",
                            "lte": "09-02-2016",
                            "format": "MM-dd-yyyy"
                        }
                    }
                }
            ]
        }
    }
}

Let's say you have an index like

{
  "mappings": {
    "properties": {
      "start_date": {
        "type": "date",
        "format": "date_optional_time"
      }
    }
  }
}

with data

{"index": {}}
{"start_date": "2022-10-10"}
{"index": {}}
{"start_date": "2022-10-11"}
{"index": {}}
{"start_date": "2022-10-12"}

You can search the index with the query below.

{
  "query": {
    "range": {
      "start_date": {
        "gte": "10-09-2022",
        "lte": "10-11-2022",
        "format": "MM-dd-yyyy"
      }
    }
  }
}

Collectives™ on Stack Overflow

How can I make query between dates in ElasticSearch?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related