date fields not being treated as timestamp fields in Kibana index patterns

Question

I have been trying to get the mapping correct for my date values in my documents. I have come down to doing a lot of hit and trial now with it. Every time and no matter what option I try, I am not getting the date field in my document treated as a timestamp column while creating Kibana index patterns.

Here are the various options I have tried (hit and trial because I feel like a total noob with ELK :blush:) with my index mappings:

trial 1:

{
    "mappings" : {
        "properties" : {
                 "Created" : {
          "type" : "date",
          "fields" : {
            "keyword" : {
              "type" : "keyword"
            }
          },
          "format": "strict_date_optional_time||epoch_millis"
        },
        "Due date" : {
          "type" : "date",
          "fields" : {
            "keyword" : {
              "type" : "keyword"
            }
          },
          "format": "strict_date_optional_time||epoch_millis"
        },
        "Updated" : {
          "type" : "date",
          "fields" : {
            "keyword" : {
              "type" : "keyword"
            }
          },
          "format": "strict_date_optional_time||epoch_millis"
        }
      }
    }
}

trial 2

    {
    "mappings" : {
        "properties" : {
                 "Created" : {
          "type" : "date",
          "fields" : {
            "keyword" : {
              "type" : "keyword"
            }
          }          
        },
        "Due date" : {
          "type" : "date",
          "fields" : {
            "keyword" : {
              "type" : "keyword"
            }
          }
        },
        "Updated" : {
          "type" : "date",
          "fields" : {
            "keyword" : {
              "type" : "keyword"
            }
          }
        }
      }
    }
}

trial 3

    {
    "mappings" : {
        "properties" : {
                 "Created" : {
          "type" : "date",
          "fields" : {
            "keyword" : {
              "type" : "date"
            }
          }          
        },
        "Due date" : {
          "type" : "date",
          "fields" : {
            "keyword" : {
              "type" : "date"
            }
          }
        },
        "Updated" : {
          "type" : "date",
          "fields" : {
            "keyword" : {
              "type" : "date"
            }
          }
        }
      }
    }
}

And then each of the trials above with the below :

    "_default_": {
  "_timestamp": {
    "enabled": true,
    "store": true,
    "_field_names": "_timestamp"
  }
},

and then each of the

  "fields" : {
    "keyword" : {
      "type" : "date"
    }

specified with the format again explicitly with each of the above combinations. and that’s how my sample document looks like (different trials)

trial doc 1

{ "Created": "15/11/21 13:21",
  "Updated": "30/12/21 14:30",
  "Due date": null
}

tried doc 2

{ 
  "Created": 1636982460000,
  "Updated": 1640874600000,
  "Due date": null
}

and none of the above combinations seems to make the needed fields as a timestamp field while creating the index patterns in Kibana, (Elasticsearch & Kibana 7.15)

Plus when sending the documents with epoch time (as in trial document 2), despite defining the mapping explicitly for the index, when I look at the mapping from Kibana -> Index Management, it always shows up as long as shown below :thinking_face:

While in case of the trial document 1 the same always shows up as text as below, despite again having the mapping explicitly defined as a date type (with or without the format specified)

I am sure there is a lot to learn here. Could someone please guide in the right direction? Lest I end up writing an automation for generating the various combinations (and ofcourse that would be brainless & for sure not lead to any good :smile: )

Here's how my pushing the data to the index:

from elasticsearch import Elasticsearch

#with open('asb-23-01-2022-18-36-46.json') as f: # with epoch
with open('asb-24-01-2022-19-57-44.json') as f: # with date
    x= json.load(f)

es = Elasticsearch([{'host': 'localhost', 'port': '9200'}], http_auth=('elastic', 'password'))

mapping = '''
{
      "properties" : {
        "Created" : {
          "type" : "keyword",
          "fields" : {
            "keyword" : {
              "type" : "date",
              "format":"dd/MMM/yy h:m a"
            }
          }
        },
        "Due date" : {
          "type" : "keyword",
          "fields" : {
            "keyword" : {
              "type" : "date",
              "format":"dd/MMM/yy h:m a"
            }
          }
        }
        "Module" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
    }
}
'''
es.indices.exists(index="'index-name'") or es.indices.create(index='index-name', ignore=400, body=mapping)
for item in x:
    print(x.index(item))
    es.index(index='index-name', body=item, ignore=400)

I run the above code directly on the elasticsearch data node to push the data.