CODEWITHSUNDEEP
elasticsearch
Edmondo
Edmondo

Reputation: 20090

Filtering on a mapped timestamp

I have been moving from an auto-generated timestamp to a mapped one and query doesn’t work anymore. When using an auto-generated timestamp, I was able to perform this sort of query:

FilterBuilder filter = FilterBuilders.rangeFilter("_timestamp").from(zonedDateTime.toLocalDate().toString()).to(zonedDateTime.plusHours(1).toLocalDate().toString());

but also passing date expressions such as “now-1h” would work. Now that I have introduced the following mapping:

"collections": {
  "_timestamp": {
    "enabled": true,
    "path": "my_date",
    "ignore_missing": false
  },
  "properties": {
    "author": {
      "type": "string",
      "index": "not_analyzed"
    },
    "name": {
      "type": "string",
      "analyzer": "lowercase"
    },
    "my_date": {
      "type": "date"
    }
  }
}

And I store my_date as a Unix EPOCH format, I can’t query anymore:

FilterBuilders.rangeFilter("_timestamp").from(zonedDateTime.toLocalDate().toString()).to(zonedDateTime.plusHours(1).toLocalDate().toString());

Return empty results, while the query

FilterBuilders.rangeFilter("my_date").from(zonedDateTime.toLocalDate().toString()).to(zonedDateTime.plusHours(1).toLocalDate().toString());

Fails with an exception

from[-1],size[-1]: Parse Failure [Failed to parse source [{"query":{"filtered":{"query":{"match_all":{}},"filter":{"range":{"my_date":{"from":"2015-09-04T19:52:15.001+01:00","to":"2015-09-04T21:52:15.001+01:00","include_lower":true,"include_upper":true}}}}}}]]]; nested: NumberFormatException[For input string: "2015-09-04T19:52:15.001+01:00"];

It looks like the only query that I can do is by using the numeric values of the timestamp, on my_date. Is there anything better I can hope for?

Upvotes: 0

Views: 486

Answers (1)

imotov
imotov

Reputation: 30163

There is still something missing in your question. I can only guess that you might have posted the number of seconds since epoch instead of number of milliseconds, your mapping was not applied properly, so something else went wrong that you didn't disclose yet. Here is example based on the data that you have chosen to reveal so far and it works perfectly fine. Please try to modify the example to match your situation.

curl -XDELETE localhost:9200/test
curl -XPUT localhost:9200/test -d '{
    "mappings": {
        "collections": {
            "_timestamp": {
                "enabled": true,
                "path": "my_date",
                "ignore_missing": false
            },
            "properties": {
                "my_date": {
                    "type": "date"
                }
            }
          }
        }
    }
}'
curl -XGET "localhost:9200/test/_mapping?pretty"
curl -XPOST "localhost:9200/test/collections?refresh" -d '{"my_date": "1441421651000"}'
curl -XGET "localhost:9200/test/collections/_search?pretty" -d '{
    "query": {
        "filtered": {
            "query": {
                "match_all": {}
            },
            "filter": {
                "range": {
                    "my_date": {
                        "from": "2015-09-04T19:52:15.001+01:00",
                        "to": "2015-09-05T21:52:15.001+01:00",
                        "include_lower": true,
                        "include_upper": true
                    }
                }
            }
        }
    }
}'

Upvotes: 3

Related Questions