Date Histogram - group by hour interval across multiple days

[prashant.bajpai (pBajpai)]

Today I had a task where I have to aggregate the data bucketed by 1 hour interval. So I used Date_Histogram aggregation in elastic search. Below is the query:

    GET test-2017.02.01/_search
    {
      "size" : 0,
    
      "aggs": {
        "range_aggs": {
          "date_histogram": {
            "field": "@timestamp",
            "interval": "hour",
            "format": "yyyy-MM-dd HH:mm"
          }
        }
      }
      
    }

I got the below result:

        "aggregations": {
        "range_aggs": {
          "buckets": [
            {
              "key_as_string": "2017-02-01 12:00",
              "key": 1485950400000,
              "doc_count": 4027
            },
            {
              "key_as_string": "2017-02-01 13:00",
              "key": 1485954000000,
              "doc_count": 0
            }
          ]
        }
      }

Every is good till now as I have run this query for one day, but when I run the query for multiple days in that case, I am getting the keys per day.
My question is - How can I get the data for the hour intervals(ex- 9am to 10am, 10am to 11am, ...etc) across all the days ?

[dadoonet (David Pilato)]

Add this field in your model, like hour_of_the_day.
Then it will be easy to create a terms agg on it

[prashant.bajpai (pBajpai)]

@dadoonet Thank for your reply, Is this the only way ? , If I don't want to make any change in elastic db, in that case can we have any solution ?

[dadoonet (David Pilato)]

Terms agg with script https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-script

But that will be probably slow.