Date Math incorrectly returns documents outside the range

[cawoodm]

ELK 6.6.1 Windows

When querying docs for yesterday(>= now-1d/d AND < now/d), docs for today are returned.

GET /myindex/_search?sort=timestamp:desc
{
  "from" : 0, "size" : 100,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "timestamp": {
                "gte": "now-1d/d",
                "lt": "now/d"
            }
          }
        }
      ]
    }
  }
}

A document of timestamp today 00:23:57.084 (CET) is returned:

   "_id": "Z4d3wWkBV0vWSMeR5iew",
   "_source": {
      "timestamp": "2019-03-27T23:23:57.084Z",
   ...

[elasticmachine]

Pinging @elastic/es-search

[javanna]

This looks like a timezone issue, times are stored as UTC within Elasticsearch, and this document has timestamp from 23:23:57 which does match your range query. The UI seems to apply a timezone +1 to it when visualizing it though. Note that you can provide a timezone as part of the range query if you want to leave your query as is, otherwise you would have to convert your range to UTC manually. Does this make sense to you?

[cawoodm]

I will try this (timezone) on monday and get back to you. For now I am wondering what timezone ES is defaulting to? Our server is CET, our client is CET, we never configured anything so I would expect a sensible default and not, say EST or GMT.

[cbuescher]

Our server is CET, our client is CET, we never configured anything so I would expect a sensible default and not, say EST or GMT.

That is UTC, like @javanna already mentiond. More info in the docs. Also I just checked that timezone is supported in date math expressions with rounding in range queries like in the example you mentioned.

[cawoodm]

OK, I can confirm that works.

"timestamp": {
    "gte": "now-1d/d",
    "lt": "now-0d/d",
    "time_zone": "+01:00"
}

[cawoodm]

Little help? Just trying to do a query with local dates (CET). I'm getting "failed to parse date field [2019-03-31 00:00:00] with format [date_time||epoch_millis]". I've tried "gte": "2019-03-31 00:00:00", "gte": "2019-03-31 00:00:00.000", "gte": "2019-03-31 00:00:00.0000000000" and T as a separator to no avail..