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

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

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

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

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

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

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]
}

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

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]
}

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:

And it almost works. Displaying works — this shows a 10ms time window — but the sort order is not correct:

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

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

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:

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:

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.