How to group by month in Elastic search

Question

I am using elastic search version 6.0.0 for group by month, I am using date histogram aggregation. example which I've tried :

{  
   "from":0,
   "size":2000,
   "_source":{  
      "includes":[  
         "cost",
         "date"
      ],
      "excludes":[  

      ],
      "aggregations":{  
         "date_hist_agg":{  
            "date_histogram":{  
               "field":"date",
               "interval":"month",
               "format":"M",
               "order":{  
                  "_key":"asc"
               },
               "min_doc_count":1
            },
            "aggregations":{  
               "cost":{  
                  "sum":{  
                     "field":"cost"
                  }
               }
            }
         }
      }
   }
}

and as a result i got 1(Jan/January) multiple times. As I have data of January-2016 ,January-2017 , January-2018 so will return 3 times January. but i Want January only once which contains the sum of All years of January.

score 9 · Accepted Answer · answered Aug 16 '18 at 08:36

9

Instead of using a date_histogram aggregation you could use a terms aggregation with a script that extracts the month from the date.

{
  "from": 0,
  "size": 2000,
  "_source": {"includes": ["cost","date"],"excludes"[]},
  "aggregations": {
    "date_hist_agg": {
      "terms": {
        "script": "doc['date'].date.monthOfYear",
        "order": {
          "_key": "asc"
        },
        "min_doc_count": 1
      },
      "aggregations": {
        "cost": {
          "sum": {
            "field": "cost"
          }
        }
      }
    }
  }
}

Note that using scripting is not optimal, if you know you'll need the month information, just create another field with that information so you can use a simple terms aggregation on it without having to use scripting.

answered Aug 16 '18 at 08:36

Val

207,596
13
358
360

Is there any other way for this ? cause sometimes i got BAD GATEWAY error due to execution of script – kavi Aug 24 '18 at 06:37
@kpatel Yes, I've explained another solution here: https://stackoverflow.com/questions/37585988/day-of-month-aggregation/37608680#37608680 – Val Aug 24 '18 at 06:39
can you explain why the `time_zone feature is not supported`? – Val Aug 29 '18 at 07:43

Ovinz · Answer 2 · 2022-09-19T08:13:38.020

We can use the calendar_interval with month value:

Documentation: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html#calendar_interval_examples

GET my_index/_search
{
  "size": 0, 
  "query": {},
  "aggs": {
    "over_time": {
      "date_histogram": {
        "field": "yourDateAttribute",
        "calendar_interval": "month",
        "format": "yyyy-MM" // <--- control the output format
      }
    }
  }
}

How to group by month in Elastic search

2 Answers2