Elasticsearch query range precision problem for small values

user_1087653 0
Viewed 231

I've a database with a few thousand values-timestamp pairs I want to query and filter. Typical timestamps are:

2019-06-15T15:09:14.0191875+02:00
2019-06-15T15:09:14.0195000+02:00
2019-06-15T15:09:14.0194375+02:00
2019-06-15T15:09:14.0198750+02:00
2019-06-15T15:09:14.0190000+02:00
2019-06-15T15:09:14.0190625+02:00
2019-06-15T15:09:14.0191875+02:00

As you can see, they only vary in the range of microseconds. When I use the following query, I receive all those values.

{
 "size": 500,
 "sort": [
  {
   "timestamp": {
   "order": "desc"
  }}],
 "query": {
   "range": {
     "timestamp": {
      "time_zone": "+02:00",
      "gt": "2019-06-15T15:09:14.01",
      "lt": "2019-06-15T15:09:14.02"
     }
    }
   }
 }

When I change gt and lt to e.g. "2019-06-15T15:09:14.0191" and "2019-06-15T15:09:14.0196", the result is empty. The same happens for the value query.

65.59359741210938
0.149688720703125
0.0748443603515625
0.014968873001635075
0.2395019680261612

If I use the this query

{
  "size": 1000,
  "sort": [
    {
      "value": {
      "order": "asc"
      }
    }
  ],
  "query": {
    "range" : {
      "value": {
        "gte": 0
      }
    }
  }
}

I am getting results including the aforementioned values. When I change gte to 0.02 and add "lte": 0.09 the result is empty. Can someone explain to me what I am doing wrong here?

elasticsearch json
1 Answers

Good question. Your timestamp is probably of type date which stores dates in millisecond resolution. So what you need is date_nanos.

Drop the index, change the mapping to

{
  "mappings": {
    "properties": {
      "timestamp": {
        "type": "date_nanos"
      }
    }
  }
}

then reindex and you're good to go.

Note that this is only supported in 7.0 and onwards. Here's the story of the journey from millis to nanos.

user_81603180
Related