Elasticsearch query date range does not work

Question

I am trying to query ElasticSearch to get a result between two timestamps. A typical record looks like

{
   "_index": "cost-2018.08.09",
   "_type": "log",
   "_id": "asdasdasxsa-sdsds",
   "_score": 4.281278,
   "_source": {
      "index": "cost-2018.08.09",
      "app_group": "shop",
      "timestamp": "2018-08-09T00:00:04.349692"
   }
}

And the query I use to retrieve every shop from app_group between 2 timestamps:

GET /cost-2018.08.09/_search?q=app_group:shop 
{
  "query": {
    "range": {
      "timestamp": {
        "gte": "2018-08-09 04:00:04",
        "lt": "2018-08-09 04:30:06"
      }
    }
  }

Only returns every shop, but does not check for any timestamp. What's weird, is that even if I include error on purpose on the query:

GET /cost-2018.08.09/_search?q=app_group:shop
{
  "query": {
    "range": {
      "timestamp": {
        "gte": "2018-08-asdadsx09 04:00:04",
        "lt": "2018-08-09asdasd 04:30:06"
      }
    }
 }

I got the exact same answer. Like it does not take the query into consideration.

Some notes: In Python, my code looks like:

result = es_client.search(index='cost-2018.07.26', q='app_group:shop', filter_path=['hits.hits._source'], body={
        "query": {
            "range": {
              "timestamp": {
                 "gte": "2018-08-09 04:00:04",
                 "lt": "2018-08-09 04:30:06"
              }
           }
         }
    })

The timestamp field of the record is indeed interpreted as a date and not a String.

What do I miss?

Answer 1

(Note that this answer is for Elasticsearch 6.3)

What worked for me was adding a mapping when creating the index.

In the mapping you specify what kind of data your field will hold and in the case of a date you can also set a format.

{
    "mappings":{
        "_doc":{
            "timestamp": {
                "format": "yyyy-MM-dd'T'HH:mm:ss'Z'",
                "type": "date"
            },
        }
    }
}

In this example the format is for a specific use case I had, but you can set it up however you need.

This should allow you to make a date range query, like in the following example:

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "app_group": "shop"
          }
        }
      ],
      "filter": [
        {
          "range" : {
            "timestamp" : {
                "gte": "2018-08-15T00:00:00Z", 
                "lte": "2018-08-15T23:00:00Z"
                }
            }
        }
      ]
    }
  }
}

Note that the format I'm using is slightly different than yours.

Some more details:

In my particular case I had an issue with the results, since I needed an exact match of the search term, to avoid related but irrelevant results.

In your case it seems like you might also run into this issue since you are searching for a specific "app_group".

To enable the exact search you can use the following mapping:

{
  "settings":{
    "index":{
      "analysis":{
        "analyzer":{
          "analyzer_case_insensitive":{
            "tokenizer":"keyword",
            "filter":"lowercase"
          }
        }
      }
    }
  },
  "mappings":{
    "_doc":{
      "properties":{
        "app_group":{
          "type":"string",
          "analyzer":"analyzer_case_insensitive"
        }
      }
    }
  }
}

(Which I found in this very useful blog post, after running into several outdated methods in StackOverflow and elsewhere)

Basically what the provided settings are doing is tell the indexer to use the keyword tokenizer and apply the lowercase filter so that your search is case-insensitive (i.e. will turn everything into lowercase first, so you can search for "app_group = shop" or "app_group = Shop", etc.

The final mapping should look similar to this (except with your own date format):

{
  "settings":{
    "index":{
      "analysis":{
        "analyzer":{
          "analyzer_case_insensitive":{
            "tokenizer":"keyword",
            "filter":"lowercase"
          }
        }
      }
    }
  },

  "mappings": {
    "_doc": {
      "properties": {
        "timestamp": {
          "type":   "date",
          "format": "yyyy-MM-dd'T'HH:mm:ss'Z'"
        },
        "app_group": {
          "type":"text",
          "analyzer":"analyzer_case_insensitive"
        }
      }
    }
  }
}