A date field with date_nanos format can not be used as an index pattern time field.

[tsullivan]

Kibana version: 8.0.0/master

Elasticsearch version: 8.0.0/master

Steps to reproduce:

1. Create an index that has a date_nanos field:

   PUT my_index?include_type_name=true
   {
     "mappings": {
       "_doc": {
         "properties": {
           "date": {
             "type": "date_nanos" 
   ...
   

   PUT my_index/_doc/1
   { "date": "2015-01-01" }
   

2. Create an index pattern for the index

3. date shows as a time field

4. If you choose the date_nanos field as the time field, Discover will send erroneous queries to Elasticsearch.

Error message:

Request to Elasticsearch failed: {"error":{"root_cause":[{"type":"illegal_argument_exception","reason":"date[1967-10-29T08:00:30Z] is before the epoch in 1970 and cannot be stored in nanosecond resolution"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"my_index","node":"nMrM4ZZTSh68Xg5kWsl8iA","reason":{"type":"illegal_argument_exception","reason":"date[1967-10-29T08:00:30Z] is before the epoch in 1970 and cannot be stored in nanosecond resolution"}}],"caused_by":{"type":"illegal_argument_exception","reason":"date[1967-10-29T08:00:30Z] is before the epoch in 1970 and cannot be stored in nanosecond resolution","caused_by":{"type":"illegal_argument_exception","reason":"date[1967-10-29T08:00:30Z] is before the epoch in 1970 and cannot be stored in nanosecond resolution"}}},"status":400}

[tsullivan]

Probably related to #37211

Looking at the code, it looks like the index pattern creation wizard should prevent the date_nanos field being used as the time field.

[elasticmachine]

Pinging @elastic/kibana-app

[tsullivan]

cc @kertal

[kertal]

I've tried to reproduce the problem with you testdata without a problem. It's interesting that elasticsearch is retrieving a date of 1967. Since I don't think that you're currently time traveling, could you take a look which range query is sent to elasticsearch by kibana? Thx!

[tsullivan]

Isolated to this part of the Discover query:

  "aggs": {
    "2": {
      "date_histogram": {
        "field": "date",
        "fixed_interval": "30d",
        "min_doc_count": 1,
        "time_zone": "America/Phoenix"
      }
    }
  },

[tsullivan]

Simplest test case, without Discover:

DELETE /my_index

PUT my_index?include_type_name=true
{
  "mappings": {
    "_doc": {
      "properties": {
        "date": {
          "type": "date_nanos" 
        }
      }
    }
  }
}

PUT my_index/_doc/2
{ "date": "2019-07-15T00:00:00.123456789Z" } 


GET /my_index/_search
{
  "aggs": {
    "2": {
      "date_histogram": {
        "field": "date",
        "fixed_interval": "30d",
        "min_doc_count": 1,
        "time_zone": "America/Phoenix"
      }
    }
  }
}

[kertal]

I could reproduce it, and also make it work by removing the time_zone, or using another timezone

GET /my_index/_search
{
  "aggs": {
    "2": {
      "date_histogram": {
        "field": "date",
        "fixed_interval": "30d",
        "min_doc_count": 1
      }
    }
  }
}

it's an Elasticsearch issue

[tsullivan]

Possibly close this issue and follow: elastic/elasticsearch#39107

[timroes]

Closing this in favor of the upstream issue elastic/elasticsearch#39107