Spring Data Elasticsearch Query Date Format

Question

We are using the spring data elasticsearch library to query our elasticsearch server. We are currently using a rest call to get the results and have been successful, but we want to use the library.

The working rest query we are sending resembles

{
    "query": {
        "bool": {
            "must": [
                { "range" : { "startDateTime" : { "from" : "2016-01-31T00:00:00", "to" : "2016-02-01T00:00:00" }}},
                { "match_phrase" : { "keyword" : "task" }}
            ]
        }
    }
}

Using the spring data elasicsearch query derivation tool we created the method

findMessagesByKeywordAndStartDateTimeBetween(String keyword, String start, String end);

Which derives to the query

{
    "from": 0,
    "query": {
        "bool": {
            "must": [
                {"query_string":{"query":"\"tasks\"","fields":["keyword"]}},
                {"range":{"startDateTime":{"from":"2016-01-31T00:00:00","to":"2016-02-01T00:00:00","include_lower":true,"include_upper":true}}}
            ]
        }
    }
}

I can run this query in a rest client and receive data, however, when the library attempts to query the database, I receive an error

{
  "timestamp": 1454360466934,
  "status": 500,
  "error": "Internal Server Error",
  "exception": "org.elasticsearch.action.search.SearchPhaseExecutionException",
  "message": "Failed to execute phase [query_fetch], all shards failed; shardFailures {
    [██████████████████████][████████████][0]: RemoteTransportException[
        [██████-██-███-███████][inet[/██.███.███.███:9300]]
            [indices:data/read/search[phase/query+fetch]]]; nested: SearchParseException[
                [████████████][0]: from[0],size[10]: Parse Failure [
                    Failed to parse source [
                        {\"from\":0,\"size\":10,\"query\":{\"bool\":{\"must\":[{\"query_string\":{\"query\":\"\"/tasks\"\",\"fields\":[\"method\"]}},{\"range\":{\"startDateTime\":{\"from\":\"2016-01-31T00:00:00.000Z\",\"to\":\"2016-02-01T00:00:00.000Z\",\"include_lower\":true,\"include_upper\":true}}}]}}}
                    ]   
                ]
            ]; 
    nested: NumberFormatException[For input string: \"2016-01-31T00:00:00.000Z\"]; 
  }",
  "path": "/report/tasks"
}

This leads us to believe that the date we are asking for is not in the correct format to reference against database items, but a sample result looks like

{
    "_index": "████████████",
    "_type": "████████████",
    "_id": "████████████",
    "_score": 0.000,
    "_source": {
      "keyword": "tasks",
      "endDateTime": "2016-01-15T00:57:31.427Z",
      "startDateTime": "2016-01-15T00:57:30.201Z",
      "@timestamp": "2016-01-15T00:57:31+00:00",
      "responseBody": "{...stuff goes here...}"
    }
},...

So you would think that you would be able to query using that format.

---

We decided to attempt to get all results with the tasks keyword using a new query

findMessagesByKeyword(String keyword);

which derives to

{
    "from": 0,
    "query": {
        "bool": {
            "must": [
                {"query_string":{"query":"\"tasks\"","fields":["keyword"]}}
            ]
        }
    }
}

This returns all the results in a page and after printing the mapped objects startDateTime and responseBody fields to the console

10: [
    [Thu Oct 15 18:55:53 EDT 2015, {...stuff goes here...}]
    [Thu Oct 15 18:56:38 EDT 2015, {...stuff goes here...}]
    [Thu Oct 15 18:56:49 EDT 2015, {...stuff goes here...}]
    [Thu Oct 15 18:58:59 EDT 2015, {...stuff goes here...}]
    [Thu Oct 15 18:59:16 EDT 2015, {...stuff goes here...}]
    [Thu Oct 15 18:59:33 EDT 2015, {...stuff goes here...}]
    [Thu Oct 15 18:59:54 EDT 2015, {...stuff goes here...}]
    [Thu Oct 15 19:00:02 EDT 2015, {...stuff goes here...}]
    [Thu Oct 15 19:00:02 EDT 2015, {...stuff goes here...}]
    [Thu Oct 15 19:00:11 EDT 2015, {...stuff goes here...}]
] //These are paged results, there are results as recently as last week, just not in this page 

I notice that the date time field is now in a different format, so I use the format

public String DATETIME_FORMAT = "EE MMM dd HH:mm:ss zz yyyy";

instead of

public String DATETIME_FORMAT = "yyyy-MM-dd'T'00:00:00.000'Z'";

and get the error

NumberFormatException[For input string: \"Sun Jan 31 00:00:00 EST 2016\"]

---

• The mapping for the field, if that helps, is

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

• We have tried many formats and datatypes. When we changed the format to

  yyyMMddHHmmss

  We no longer receive the error, but get no results.

---

At this point we know we must be doing something wrong, but are not sure where to go. All help is greatly appreciated.

---

Feb 2, 2016 10:15AM

Thanks to @Richa: After converting the date to long (in milliseconds) the query seems to run, but there are no results returned.

This is run on the default timerange of from yesterday until today, and on a manual timerange of about 10 days which I know have about 300 records.

I am also able to verify using the current rest implementation that there is data, and I am able to use a rest client to triple check, but no data for spring data impl.

Thoughts?

Answer 1

I got this error in my project too.Got it resolved using Long datatype. The dynamic finder provided by spring Data which you are using is taking date argument in String. Convert date to milliseconds and pass date in Long. Use method as:

findMessagesByKeywordAndStartDateTimeBetween(String keyword, Long start, Long end);

Hope this helps.