ElasticSearch: How to query a date field using an hours-range filter

19
votes

Currently, I already know how to filter a days range from a (timestamp) date field. That's an easy one:

"range": {
    "date": {
        "gte": "2015-11-01",
        "lte": "2015-11-30"
    }
}

But how to filter dates when you are interested in ranges based on hours like gte:"8:00:00" and lte:"10:00:00"? Is this possible?

My requirement in other words: How to get all the events happening this month (15-11-01/15-11-30) but only between 8:00:00 am and 10:00:00?

3
elasticsearch
Between those hours, for any day? – Nathan Tuggy
Let's say I want to get all the records between 2015-10-01 and 2015-12-31, but only those that happened between this hour range: "8:00:00" and "10:00:00" – xtingray
Possible duplicate of Elasticsearch: Filtering by date and time as different fields – Val
Similar question, but not the same. In the other one, I assume that there is another field called "time", that changes the problem. – xtingray

3 Answers

20
votes

You can do it with your range filter to filter the correct days and then with a script filter to filter the desired hours, like this:

{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "range": {
                "date": {
                  "gte": "2015-11-01",
                  "lte": "2015-11-30"
                }
              }
            },
            {
              "script": {
                "script": {
                  "source": "doc.date.date.getHourOfDay() >= params.min && doc.date.date.getHourOfDay() <= params.max",
                  "params": {
                    "min": 8,
                    "max": 10
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}

Note that you need to make sure to enable dynamic scripting in order for this query to work.

4
votes

If I understood your question correctly then I think you have to add new field which indexes only time like

PUT your_index
{
  "mappings": {
    "your_type": {
      "properties": {
        "time": {
          "type":   "date",
          "format": "HH:mm:ss"
        }
      }
    }
  }
}

Then you can query like this

{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "date": {
              "gte": "2015-11-01",
              "lte": "2015-11-30"
            }
          }
        },
        {
          "range": {
            "time": {
              "gte": "08:00:00",
              "lte": "10:00:00"
            }
          }
        }
      ]
    }
  }
}

Does this help?

3
votes

Here's what I once used to only get results from start of current day to 6pm:

{
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "(log_message:\"My Search String\")"
          }
        },
        {
          "range": {
            "@timestamp": {
              "time_zone": "CET",
              "gt": "now-24h/d",
              "lte": "now-24h/d+18h"
            }
          }
        }
      ]
    }
  }
}

the important part is "now-24h/d" which will round to midnight / begin of current day, although it is a bit tricky as it depends on whether you use gt/lt(e), see reference doc for details.