Elastic Search Date Range Query

Question

I am new to elastic search and I am struggling with date range query. I have to query the records which fall between some particular dates.The JSON records pushed into elastic search database are as follows:

                "messageid": "Some message id",
                "subject": "subject",
                "emaildate": "2020-01-01 21:09:24",
                "starttime": "2020-01-02 12:30:00",
                "endtime": "2020-01-02 13:00:00",
                "meetinglocation": "some location",
                "duration": "00:30:00",
                "employeename": "Name",
                "emailid": "abc@xyz.com",
                "employeecode": "141479",
                "username": "username",
                "organizer": "Some name",
                "organizer_email": "cde@xyz.com",

I have to query the records which has start time between "2020-01-02 12:30:00" to "2020-01-10 12:30:00". I have written a query like this :

{
   "query":
      {
        "bool":
           {

              "filter": [
                {
                    "range" : {
                        "starttime": { 
                             "gte": "2020-01-02 12:30:00", 
                             "lte": "2020-01-10 12:30:00" 
                         }
                    }
                }
              ]            
           } 
      }
}

This query is not giving results as expected. I assume that the person who has pushed the data into elastic search database at my office has not set the mapping and Elastic Search is dynamically deciding the data type of "starttime" as "text". Hence I am getting inconsistent results. I can set the mapping like this :

PUT /meetings
{
  "mappings": {
    "dynamic": false,
    "properties": {
        .
        .
        .
        .
      "starttime": {
        "type": "date",
        "format":"yyyy-MM-dd HH:mm:ss"
      }
        .
        .
        .
    }
  }
}

And the query will work but I am not allowed to do so (office policies). What alternatives do I have so that I can achieve my task.

Update :

I assumed the data type to be "Text" but by default Elastic Search applies both "Text" and "Keyword" so that we can implement both Full Text and Keyword based searches. If it is also set as "Keyword" . Will this benefit me in any case. I do not have access to lots of stuff in the office that's why I am unable to debug the query.I only have the search API for which I have to build the query.

GET /meetings/_mapping output :

  '
  '
  '
 "starttime" : {
        "type" : "text",
        "fields" : {
          "keyword" : {
            "type" : "keyword",
            "ignore_above" : 256
          }
        }
  }

    '
    '
    ' 

Answer 1

As @jzzfs suggested the idea is to add a date sub-field to the starttime field. You first need to modify the mapping like this:

PUT meetings/_mapping
{
  "properties": {
     "starttime" : {
        "type" : "text",
        "fields" : {
          "keyword" : {
            "type" : "keyword",
            "ignore_above" : 256
          },
          "date": {
            "type" : "date",
            "format" : "yyyy-MM-dd HH:mm:ss",
          }
        }
     }
  }
}

When done, you need to reindex your data using the update by query API so that the starttime.date field gets populated and index:

POST meetings/_update_by_query

When the update is done, you'll be able to leverage the starttime.date sub-field in your query:

{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "starttime.date": {
              "gte": "2020-01-02 12:30:00",
              "lte": "2020-01-10 12:30:00"
            }
          }
        }
      ]
    }
  }
}