Elastic search range dates

Question

I have created an Elastic search index from a Mongo database. The documents in Mongo have the following structure:

{
    "_id" : ObjectId("525facace4b0c1f5e78753ea"),
    "time" : ISODate("2013-10-17T09:23:56.131Z"),
    "type" : "A",
    "url" : "www.google.com",
    "name" : "peter",
}

The index was created (apparently) without any problems. Now, I am trying to use Elastic Search to retrieve the documents in the index between two dates. I have read that I have to use range queries, but I have tried many times things like

MatchQueryBuilder queryBuilder = QueryBuilders.matchQuery("name", "peter").type(Type.PHRASE).minimumShouldMatch("99%");
LocalDateTime toLocal = new LocalDateTime(2013,12,18, 0, 0);
Date to = toLocal.toDate();
LocalDateTime fromLocal = new LocalDateTime(2013,12,17, 0, 0);
Date from = fromLocal.toDate();
RangeQueryBuilder queryDate = QueryBuilders.rangeQuery("time").to(to).from(from);
FilterBuilder filterDate = FilterBuilders.queryFilter(queryDate);       

srb = esH.client.prepareSearch("my_index");
srb.setQuery(queryBuilder);
srb.setFilter(filterDate);
sr = srb.execute().actionGet();

and I get 0 hits although there should be many results. I have tried to enter strings instead of dates, but same results.

When I perform a basic query without filters such as:

MatchQueryBuilder queryBuilder = QueryBuilders.matchQuery("name", "peter").type(Type.PHRASE).minimumShouldMatch("99%");

SearchRequestBuilder srb = esH.client.prepareSearch("my_index");
rb.setQuery(queryBuilder);
SearchResponse sr = srb.execute().actionGet();

I get hits with that look like this:

{
"_index" : "my_index",
"_type" : "type",
"_id" : "5280d3c2e4b05e95aa703e34",
"_score" : 1.375688, "_source" : {"type":["A"],"time":["Mon Nov 11 13:55:30 CET 2013"],"name":["peter"]}
}

Where the field time does not have the format ISODate("2013-10-17T09:23:56.131Z")anymore.

To sum up, what would be the Java code (and types) for querying between two dates (and times), taking into account the format?

Answer 1

You are probably passing the wrong field name to the range query at this line:

RangeQueryBuilder queryDate = QueryBuilders.rangeQuery("time").to(to).from(from);

It should probably be @timestamp (or the field you're using to store your timestamp) instead of time. Additionally it seems that there is no time field in Elasticsearch for the example document you included. This also points to the issue that the time field wasn't converted correctly from Mongo to Elasticsearch.