Date Range Filter unclear rounding behavior

[aaneja]

If I run the query :

{
  "size": 14,
  "_source": "context.data.eventTime",
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "range": {
                "context.data.eventTime": {
                  "gte": "2014-11-12T14:54:59.000Z",
                  "lte": "2014-11-12T14:55:00.000Z"
                }
              }
            }
          ]
        }
      }
    }
  }
}

I get :

{
    "took": 0,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "failed": 0
    },
    "hits": {
        "total": 3,
        "max_score": 1,
        "hits": [
            {
                "_index": "test",
                "_type": "request",
                "_id": "4a5acb28-e6e8-4dfc-b86f-80926ed82fd7",
                "_score": 1,
                "_source": {
                    "context": {
                        "data": {
                            "eventTime": "2014-11-12T14:55:00.1458607Z"
                        }
                    }
                }
            },
            {
                "_index": "test",
                "_type": "request",
                "_id": "22a6d539-06a7-4dd9-860c-2ea5ed0956cf",
                "_score": 1,
                "_source": {
                    "context": {
                        "data": {
                            "eventTime": "2014-11-12T14:55:00.5976447Z"
                        }
                    }
                }
            },
            {
                "_index": "test",
                "_type": "request",
                "_id": "554b25f3-36a9-4f84-b2a3-69f257cf1ac0",
                "_score": 1,
                "_source": {
                    "context": {
                        "data": {
                            "eventTime": "2014-11-12T14:55:00.9979586Z"
                        }
                    }
                }
            }
        ]
    }
}

eventTime has mappings :

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

Shouldn't no records match ?

Interestingly if I run the same query with the range filter using 'lt' instead of a 'lte' I get no records matched.

[clintongormley]

Hi @aaneja

Closing in favour of #8424

[aaneja]

I read the related issue and the documentation for date rounding (http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-date-format.html#date-math)

It appears that date rounding is to the nearest second - if this is so, and I want millisecond precision is my only option passing range values as UNIX millisecond timestamps ?
(If I use :

{
  "range": {
    "context.data.eventTime": {
      "gte": "1415804099000",
      "lte": "1415804100000"
    }
  }
}

I get right results - no records match)

[clintongormley]

Rounding only happens if you specify it, eg "some_date/d"

What's happening is that the date_format that you're using (dateOptionalTime) doesn't include milliseconds. So milliseconds are being ignored both in the values that you index AND in the range clause. Try using basic_date_time. See http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-date-format.html#built-in

[aaneja]

I think dateOptionalTime does include milliseconds on indexing. As you see from above one of the records has timestamp : "2014-11-12T14:55:00.1458607Z" which in UNIX milliseconds is 1415804100145.

I ran a query with

{
  "range": {
    "context.data.eventTime": {
      "gte": "1415804100145",
      "lte": "1415804100145"
    }
  }
}

and this record matched. If I try the same range filter with 1415804100146 - 1415804100149 no records match

[clintongormley]

Hmm you may be right - I'll have to take another look at this one tomorrow

[aaneja]

Ping. Any updates on what the expected behavior of dateOptionalTime should be ?

[clintongormley]

Hi @aaneja

So you're correct: dateOptionalTime does include milliseconds. But I can't replicate your findings. Your query correctly returns no results when I run it. I tried on 1.2.1, 1.3.4, and 1.4.0.

Do you perhaps have another field with the same name but a different mapping? Can you provide a working recreation of this issue? What version of Elasticsearch are you running?

[aaneja]

Here's a repro -
GET /
Response -

{
   "status": 200,
   "name": "Terror",
   "version": {
      "number": "1.3.2",
      "build_hash": "dee175dbe2f254f3f26992f5d7591939aaefd12f",
      "build_timestamp": "2014-08-13T14:29:30Z",
      "build_snapshot": false,
      "lucene_version": "4.9"
   },
   "tagline": "You Know, for Search"
}

Then -

DELETE /megacorp/tweet

PUT /megacorp/_mapping/tweet
{
    "tweet" : {
        "properties": {
              "eventTime": {
                "type": "date",
                "format": "dateOptionalTime"
              }
            }
    }
}

PUT /megacorp/tweet/1?pretty
{
  "eventTime": 1415832900146
}

PUT /megacorp/tweet/2?pretty
{
  "eventTime": 1415832900597
}


POST /megacorp/tweet/_search
{
  "size": 14,
  "_source": "eventTime",
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "range": {
                "eventTime": {
                  "gte": "2014-11-12T22:54:00Z",
                  "lte": "2014-11-12T22:55:00Z"
                }
              }
            }
          ]
        }
      }
    }
  }
}


POST /megacorp/tweet/_search
{
  "size": 14,
  "_source": "eventTime",
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "range": {
                "eventTime": {
                  "gte": 1415832840000,
                  "lte": 1415832900000
                }
              }
            }
          ]
        }
      }
    }
  }
}

The last two queries should be, IMO, equivalent.
However I get incorrect results in the first one; the second one correctly matches no records

[rjernst]

I've tracked this down to a bug in the date parser and am working on a fix.

[rjernst]

I have a fix in #8834. Note that this is really a first step towards eliminating the two different parsing functions in the date math parser (see #8598).

[rjernst]

After discussing the further, I think the best immediate fix here is to set mapping.date.round_ceil = false. This should fix the problem described above (essentially doing via configuration what my patch in #8834 does). In order to not introduce breaking behavior, I am going to drop #8834 entirely and work on the full removal of mapping.date.round_ceil for #8598.

[rjernst]

I'm closing this in favor of #8598 which will be fixed for 2.0.