Elastic search (Spring data elastic search)- Date filter(range) not working as expected

Elastic Stack Elasticsearch
1

I tried to search/filter on field("timestamp"), but data are not filtered or returned as expected.

I wanted to search/filter data's based on (startDate and endDate) filter.

I tried for a single field("timestamp) range and its details as below.

1) Field declared

@Document File

  @Field(type = FieldType.Date, index = FieldIndex.not_analyzed, store = true, format = DateFormat.custom , pattern = "dd/MM/yyyy HH:mm")
 
 private String timestamp;

//Here I tried changing the field type from String to Date type; but no help.

2) Query building code

   BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();

   QueryBuilder matchAllQuery=matchAllQuery();
   boolQueryBuilder.must(matchAllQuery);


    NativeSearchQueryBuilder builder = new NativeSearchQueryBuilder().withQuery(boolQueryBuilder);

    String strStartDate = ElasticSearchUtils.convertDateToFormat(startDate,"dd/MM/yyyy HH:mm");
    String strEndDate = ElasticSearchUtils.convertDateToFormat(endDate, "dd/MM/yyyy HH:mm");
    RangeQueryBuilder rangeQueryBuilder = QueryBuilders.rangeQuery("timestamp")
            .format("dd/MM/yyyy HH:mm")
            .gte(strStartDate)
            .lte(strEndDate);

    builder.withFilter(rangeQueryBuilder);

    builder.build();

//startDate and endDate are the inputs from the client

3) Data stored in elasticdocument for the field("timestamp) as
below(totally there are 20 records)

{
...
"timestamp": "18/07/2017 01:00",
...
}

4) Api call format

a) http://localhost:8080/getDetails?startDate=13/07/2017 10:20&endDate=24/07/2017 15:30

o/p - returns data as expected

b) http://localhost:31020/articles/fuzzysearch?startDate=13/06/2017 10:20&endDate=24/06/2017 15:30

o/p - returns data as expected
Excepted - No data as filter is out of range

c) http://localhost:31020/articles/fuzzysearch?startDate=13/06/2017 10:20&endDate=17/06/2017 15:30

o/p - No data returned
Excepted - No data

No data returned as excepted

{
  "bool" : {
    "must" : {
      "match_all" : { }
    }
  }
},{
  "range" : {
    "timestamp" : {
      "from" : "13/06/2017 10:20",
      "to" : "17/06/2017 15:30",
      "format" : "dd/MM/yyyy HH:mm",
      "include_lower" : true,
      "include_upper" : true
    }
  }
}

d) http://localhost:31020/articles/fuzzysearch?startDate=13/06/2017 10:20&endDate=18/06/2017 15:30

Data returned - Which is wrong, as filter don't much(with the data present in the elasticsearch) -- Is it considering only day in the date during filter?

{
  "bool" : {
    "must" : {
      "match_all" : { }
    }
  }
},
{
  "range" : {
    "timestamp" : {
      "from" : "13/06/2017 10:20",
      "to" : "18/06/2017 15:30",
      "format" : "dd/MM/yyyy HH:mm",
      "include_lower" : true,
      "include_upper" : true
    }
  }
}

Questions:

a) Why data returned, even though data not present in elasticsearch with the range mentioned in step4(b)?
In the Step4(c) it works as expected. Is only day considered during filter? Confused :frowning:

b) Is the query formation/building is wrong?

c) Whether field("timestamp") storing in elasticsearch is as expected?

d) If startDate and endDate are the fields(which is of similar type of "timestamp", mentioned in step2) in the document. How I should go ahead and build a single range query, so that data's are filtered between startDate & endDate? - I tried 2 range queries, one for startDate and another for endDate. Added 2 range queries in the filter, but didn't help.

Any help is much appreciated!

2

Please format your code using </> icon as explained in this guide. It will make your post more readable.

Or use markdown style like:

```
CODE
```

I edited your post.

I'm a bit confused by:

{
  "bool" : {
    "must" : {
      "match_all" : { }
    }
  }
},
{
  "range" : {
    "timestamp" : {
      "from" : "13/06/2017 10:20",
      "to" : "18/06/2017 15:30",
      "format" : "dd/MM/yyyy HH:mm",
      "include_lower" : true,
      "include_upper" : true
    }
  }
}

It's "pseudocode" or what? I mean that the query DSL looks wrong here.
The range part should be within a filter clause which we don't see here.

Could you reproduce with a simple script what you see (outside the context of Spring) just by using Kibana REST Console? Then we can start to iterate from there.

3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.