ElasticSearch - Range filter by date is not working,

Question

I'm trying to filter a query using range by date but it's not working. If i use gt, gte, lt, lte it returns zero results. If i use only gt or lt, it returns some results but the filter is not working.

I've checked datatype on uri http://mydomain.local:9200/logstash-2014.09.09/_mapping?pretty=true the field type is correct:

"original" : {
    "type" : "date",
    "format" : "dateOptionalTime"
}

Here is an example of a result that i have indexed in ElasticSearch:

{
    "_index" : "logstash-2014.09.08",
    "_type" : "iis",
    "_id" : "wxtnfpyjR4u7dhwlEAWevw",
    "_score" : 1.0,
    "_source":{"@version":"1","@timestamp":"2014-09-08T20:55:46.460Z",
               "type":"iis","original":"14-09-08 17:39:58"}
}

And here is how i'm trying to perform a query:

{
    "query" : {
        "filtered" : {
            "filter" : {
                "range" : {
                    "original" : {
                        "gt" : "14-09-10"
                    }
                }
            }
        }
    }
}

Anyone knows what is wrong on my query? Why it returns some results if i don't have any date greater than today ( 2014-09-09 )?

Alcanzar Alcanzar · Accepted Answer · 2014-09-09T13:57:37

I created an index with the same mapping and tried to put a record in

curl -XPOST localhost:9200/test-1/x/_index -d '{"original": "14-09-08 17:39:58"}'

and got an error:

{"error":"MapperParsingException[failed to parse [original]]; nested: MapperParsingException[failed to parse date field [14-09-08 17:39:58], tried both date format [dateOptionalTime], and timestamp number with locale []]; nested: IllegalArgumentException[Invalid format: \"14-09-08 17:39:58\" is malformed at \" 17:39:58\"]; ","status":400}`

So I believe that you are in a a locale where that ##-##-## is being interpreted as something other than yy-mm-dd.

You are using Logstash so you can fix the original field before it goes in with a mutate to make it unambiguous.

mutate {
   replace => { original => "20%{original}" }
}

ElasticSearch - Range filter by date is not working,

1 Answers