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

Answer 2

Date range queries will not work on text field, for that, you have to use the date field

Since you are working on date fields , best practice is to use the date field.

I would suggest you to reindex your index to another index so that you can change the type of your text field to date field

Step1-: Create index2 using index1 mapping and make sure to change the type of your date field which is text to date type

Step 2-: Run the elasticsearch reindex and reindex all your data from index1 to index2. Since you have changed your field type to date field type. Elasticsearch will now recognize this field as date

POST _reindex
 { 
"source":{ "index": "index1" },
 "dest": { "index": "index2" }
 }

Now you can run your Normal date queries on index2

Answer 3

There are ways of parsing text fields as dates at search time but the overhead is impractical... You could, however, keep the starttime as text by default but make it a multi-field and query it using starttime.as_date, for example.