DEV Community

Philipp Krenn
Philipp Krenn

Posted on • Originally published at xeraa.net on

Combining date and date_nanos in Elasticsearch and Kibana

The Elasticsearch 7.0 release added support for nanosecond timestamps. So besides the date datatype there’s now also date_nanos. One of the more confusing things is when you combine the two datatypes, which this post is exploring.

Mapping of date_nanos

Before diving into combining datatypes, there’s one other cause of confusion: date_nanos is not automatically picked up by dynamic mapping — when you don’t provide a mapping and Elasticsearch tries to guess the datatype of each field based on the value of the first document you index.

For illustration, create two new indices with dynamic mapping and retrieve them:

PUT timestamp-millis/_doc/1
{
  "timestamp": "2019-01-01T12:10:30.124Z"
}
PUT timestamp-nanos/_doc/1
{
  "timestamp": "2019-01-01T12:10:30.124456789Z"
}
GET timestamp-*/_mapping
Enter fullscreen mode Exit fullscreen mode

Both have the same mapping, which is probably not what you intended:

{
  "timestamp-nanos" : {
    "mappings" : {
      "properties" : {
        "timestamp" : {
          "type" : "date"
        }
      }
    }
  },
  "timestamp-millis" : {
    "mappings" : {
      "properties" : {
        "timestamp" : {
          "type" : "date"
        }
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Combining date and date_nanos in Elasticsearch

Now to the combining of datatypes. Delete the existing indices and start over with the explicit mapping:

DELETE timestamp-*
PUT timestamp-millis
{
  "mappings": {
    "properties": {
      "timestamp": {
        "type": "date"
      }
    }
  }
}
PUT timestamp-nanos
{
  "mappings": {
    "properties": {
      "timestamp": {
        "type": "date_nanos"
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Also, add four sample documents close to each other:

PUT timestamp-millis/_doc/1
{
  "timestamp": "2019-01-01T12:10:30.123Z"
}
PUT timestamp-millis/_doc/2
{
  "timestamp": "2019-01-01T12:10:30.124Z"
}
PUT timestamp-nanos/_doc/3
{
  "timestamp": "2019-01-01T12:10:30.123456789Z"
}
PUT timestamp-nanos/_doc/4
{
  "timestamp": "2019-01-01T12:10:30.123498765Z"
}
Enter fullscreen mode Exit fullscreen mode

How does this behave when you search over both indices and want to sort on the field timestamp?

GET timestamp-*/_search
{
  "sort": "timestamp"
}
Enter fullscreen mode Exit fullscreen mode

Probably not the way you would expect (only including the relevant fields of hits):

{
  "_index" : "timestamp-millis",
  "_id" : "1",
  "_source" : {
    "timestamp" : "2019-01-01T12:10:30.123Z"
  },
  "sort" : [1546344630123]
},
{
  "_index" : "timestamp-millis",
  "_id" : "2",
  "_source" : {
    "timestamp" : "2019-01-01T12:10:30.124Z"
  },
  "sort" : [1546344630124]
},
{
  "_index" : "timestamp-nanos",
  "_id" : "3",
  "_source" : {
    "timestamp" : "2019-01-01T12:10:30.123456789Z"
  },
  "sort" : [1546344630123456789]
},
{
  "_index" : "timestamp-nanos",
  "_id" : "4",
  "_source" : {
    "timestamp" : "2019-01-01T12:10:30.123498765Z"
  },
  "sort" : [1546344630123498765]
}
Enter fullscreen mode Exit fullscreen mode

Or actually, if you look at the sort field and read the documentation, it does make sense:

[date_nanos] are still stored as a long representing nanoseconds since the epoch.

But this is still not what you want. Luckily the feature was added in 7.2: “Add date and date_nanos conversion to the numeric_type sort option.”

So the query you want to use is:

GET timestamp-*/_search
{
  "sort": {
    "timestamp": {
      "numeric_type": "date_nanos"
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

And it works by casting all the timestamp fields to nanosecond precision:

{
  "_index" : "timestamp-millis",
  "_id" : "1",
  "_source" : {
    "timestamp" : "2019-01-01T12:10:30.123Z"
  },
  "sort" : [1546344630123000000]
},
{
  "_index" : "timestamp-nanos",
  "_id" : "3",
  "_source" : {
    "timestamp" : "2019-01-01T12:10:30.123456789Z"
  },
  "sort" : [1546344630123456789]
},
{
  "_index" : "timestamp-nanos",
  "_id" : "4",
  "_source" : {
    "timestamp" : "2019-01-01T12:10:30.123498765Z"
  },
  "sort" : [1546344630123498765]
},
{
  "_index" : "timestamp-millis",
  "_id" : "2",
  "_source" : {
    "timestamp" : "2019-01-01T12:10:30.124Z"
  },
  "sort" : [1546344630124000000]
}
Enter fullscreen mode Exit fullscreen mode

Does This Work for Kibana?

Kibana 7.3 added general date_nanos support though there are some limitations, which are described in the Github issue for that feature:

We can’t fully support date_nanos in Kibana, due to the technical limitations of JavaScript. JavaScript stores all numeric values in a 64bit floating point number. Thus we only can represent integer (non decimal numbers) to a precision of 52 bit (the mantisse in that floating point number).

To try it out with mixed datatypes, you need to create the index pattern first and pick the field timestamp for the Time Filter field name on the following screen: Creating the index pattern in Kibana

And it almost works. Displaying works — this shows a 10ms time window — but the sort order is not correct: Discover with the default date precision of milliseconds

When you edit the index pattern and change the Format to Date Nanos: Edit the index pattern to change the format to Date Nanos

Then Discover displays the nanosecond precision of the timestamp, but it still does not fix the sorting issue: Discover with Date Nanos formatting

Why is sorting not working correctly? Because the underlying query is not using "numeric_type": "date_nanos". You can check that through the Inspect button to see both the request and the response: Request without numeric_type, so the sorting breaks on mixed fields

I have raised an issue in Kibana for a fix.

Update in 7.5

The release of Kibana 7.5.0 fixed the remaining issue. You don’t have to change the Format of the field anymore, but it just works now: Fixed sorting order in Discover on Kibana 7.5.0 and up

Conclusion

Once you are avoiding traps like dynamic mapping and watch out for "numeric_type": "date_nanos", combining date and date_nanos for sorting work in Elasticsearch and almost in Kibana.

PS: Thanks for the Stack Overflow question that led me down this rabbit hole.

Top comments (0)