So much pain on ES, query on date returns wrong results

(Derrick) #1

Hi support,

I am doing range query on date field, but it returns no expected result, any idea?

{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "booking.dealerIDUpdate": {
              "gte": "2017-11-29T00:00:00.000-0000",
              "lte": "2017-11-29T23:59:59.000-0500",
              "include_lower": true,
              "include_upper": true,
              "boost": 1,
              "time_zone": "-05:00"
            }
          }
        }
      ]
    }
  }
}
(David Pilato) #2

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

Or use markdown style like:

```
CODE
```

Please edit your post.

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

(Derrick) #3

I am using QueryBuilders to create query, I dont' k

queryBuilder.filter(QueryBuilders.rangeQuery("booking.dealerIDUpdate").from(start).to(end).timeZone("America/New_York"));

how to set timezone? with this code, it always returns some data with 2017-11-28T19:30:00

saved date in ES 5.6, it will be returned by above query, reason?

"booking": {
"dealerIDUpdate": "2017-11-28T19:36:00.000-0500"
}
(David Pilato) #4

I can't tell without a full example.

Anyway, few things to know.
If you don't define a TZ when indexing a date like 2017-11-28T19:30:00 it is supposed to be a UTC based date. That might explain what you are seeing.

So I'd probably either set the right TZ when indexing and index something like 2017-11-28T19:30:00-0500. Or I'd not use TZ at all in the range query.

(system) closed #5

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