Combining date and date_nanos in Elasticsearch and Kibana
Tags: elasticsearch, data, time, kibana
Published: 2020-10-17 23:04:16

---
title: Combining date and date_nanos in Elasticsearch and Kibana
published: true
date: 2019-08-26 00:00:00 UTC
tags: elasticsearch, data, time, kibana
canonical_url: https://xeraa.net/blog/2019_date-nano-elasticsearch-kibana/
---

The Elasticsearch 7.0 release added support for [nanosecond timestamps](https://www.elastic.co/blog/journey-support-nanosecond-timestamps-elasticsearch). So besides the [`date` datatype](https://www.elastic.co/guide/en/elasticsearch/reference/current/date.html) there’s now also [`date_nanos`](https://www.elastic.co/guide/en/elasticsearch/reference/7.0/date_nanos.html). 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](https://www.elastic.co/guide/en/elasticsearch/reference/7.0/dynamic-mapping.html) — 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:

```js
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
```

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

```js
{
  "timestamp-nanos" : {
    "mappings" : {
      "properties" : {
        "timestamp" : {
          "type" : "date"
        }
      }
    }
  },
  "timestamp-millis" : {
    "mappings" : {
      "properties" : {
        "timestamp" : {
          "type" : "date"
        }
      }
    }
  }
}
```

## Combining `date` and `date_nanos` in Elasticsearch

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

```js
DELETE timestamp-*
PUT timestamp-millis
{
  "mappings": {
    "properties": {
      "timestamp": {
        "type": "date"
      }
    }
  }
}
PUT timestamp-nanos
{
  "mappings": {
    "properties": {
      "timestamp": {
        "type": "date_nanos"
      }
    }
  }
}
```

Also, add four sample documents close to each other:

```js
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"
}
```

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

```js
GET timestamp-*/_search
{
  "sort": "timestamp"
}
```

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

```js
{
  "_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]
}
```

Or actually, if you look at the `sort` field and read the [documentation](https://www.elastic.co/guide/en/elasticsearch/reference/7.0/date_nanos.html), 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](https://github.com/elastic/elasticsearch/pull/40199): “Add date and date\_nanos conversion to the numeric\_type sort option.”

So the [query you want to use](https://www.elastic.co/guide/en/elasticsearch/reference/7.3/search-request-body.html#_sorting_numeric_fields) is:

```js
GET timestamp-*/_search
{
  "sort": {
    "timestamp": {
      "numeric_type": "date_nanos"
    }
  }
}
```

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

```js
{
  "_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]
}
```

## 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](https://github.com/elastic/kibana/issues/31424):

> 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](https://xeraa.net/blog/2019_date-nano-elasticsearch-kibana/create-index-pattern.png)

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](https://xeraa.net/blog/2019_date-nano-elasticsearch-kibana/discover-millis.png)

When you edit the index pattern and change the _Format_ to _Date Nanos_: ![Edit the index pattern to change the format to Date Nanos](https://xeraa.net/blog/2019_date-nano-elasticsearch-kibana/index-pattern.png)

Then _Discover_ displays the nanosecond precision of the timestamp, but it still does not fix the sorting issue: ![Discover with Date Nanos formatting](https://xeraa.net/blog/2019_date-nano-elasticsearch-kibana/discover-nanos.png)

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](https://xeraa.net/blog/2019_date-nano-elasticsearch-kibana/inspect.png)

I have [raised an issue in Kibana for a fix](https://github.com/elastic/kibana/issues/43939).

## Update in 7.5

The release of [Kibana 7.5.0 fixed the remaining issue](https://github.com/elastic/kibana/pull/44212). 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](https://xeraa.net/blog/2019_date-nano-elasticsearch-kibana/kibana-fixed.png)

# 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](https://stackoverflow.com/questions/57630528/bulk-inject-doc-to-elastic-search-with-nanoseconds-timestamp) that led me down this rabbit hole.