With the release of Elasticsearch 7.0 support for nanosecond timestamps was added. So besides the date datatype there is 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 we dive into combining datatypes, there is 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.

Creating two new indices with dynamic mapping and retrieving it:

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:

{
  "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. Deleting the existing indices and starting over with the explicit mapping:

DELETE timestamp-*

PUT timestamp-millis
{
  "mappings": {
    "properties": {
      "timestamp": {
        "type": "date"
      }
    }
  }
}

PUT timestamp-nanos
{
  "mappings": {
    "properties": {
      "timestamp": {
        "type": "date_nanos"
      }
    }
  }
}

And 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 try to 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 we 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 also work for Kibana?

General date_nanos support has been added in Kibana 7.3 though there are some limitations, which are discussed in detail 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 will even show you the nanosecond precision of the timestamp, but it still doesn’t fix the sorting issue: Discover with Date Nanos formatting

Why is sorting not working correctly? Because the underlying query isn’t 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 is broken on mixed fields

I’ve raised an issue in Kibana for a fix. And it will be fixed in 7.5.0.

Conclusion

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

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