Elasticsearch Aggregation by Day of Week and Hour of Day

Question

I have documents of type:

[{"msg":"hello", date: "some-date"},{"msg":"hi!", date: "some-date"}, ...

I want to have the count of documents by day of week. For example x messages were sent on Monday and y were sent on Tuesday and so on.

I have used date_histogram with aggregation but it returns me the documents day wise. It does return me the day, but say "Wed, 22" and "Wed, 29" are returned as separate aggregation documents.

This is somewhat related to Elasticsearch - group by day of week and hour but there is no answer to that question so I am reposting it. According to the suggestion there it asks me to do term aggregation on key_as_string, but I need to add doc_count for every object instead of just count the terms. I also don't know how to use key_as_string in the nested aggregation.

This is what I have tried:

"aggs" : {
                "posts_over_days" : {
                    "date_histogram" : { 
                        "field" : "created_time", 
                        "interval": "day",
                        "format": "E" 
                    }
                }

Shiah

You might want to include the aggregation you tried. – Trunkfish

I have included the aggregation.. This is the same as the answer in the link I posted. – Shiah

Answer 1

The same kind of problem has been solved in this thread.

Adapting the solution to your problem, we need to make a script to convert the date into the hour of day and day of week:

Date date = new Date(doc['created_time'].value) ; 
java.text.SimpleDateFormat format = new java.text.SimpleDateFormat('EEE, HH');
format.format(date)

And use it in a query:

{
    "aggs": {
        "perWeekDay": {
            "terms": {
                "script": "Date date = new Date(doc['created_time'].value) ;java.text.SimpleDateFormat format = new java.text.SimpleDateFormat('EEE, HH');format.format(date)"
            }
        }
    }
}

Stilted

Brilliant.. though I want both the things (hours and days) differently but that's pretty easy now! :) And I was just about to post and answer to this taking insights from #25493058 But your script works perfect without any modification. I would just give another 15 minutes to try and shorten it a bit. Thanks a lot! – Shiah

Be careful with script though, If you have less data then it is okay, But if you do have 100k data, then It have significant loss in performance, You might just want to do parsing in your application after result is returned. – Trunkfish

Answer 2

Re-post from my answer here: https://mcmap.net/q/1324511/-how-to-show-day-names-using-date-histogram-aggregation-in-elascticsearch

Does this help:

"aggregations": {
    "timeslice": {
        "histogram": {
            "script": "doc['timestamp'].value.getHourOfDay()",
            "interval": 1,
            "min_doc_count": 0,
            "extended_bounds": {
                "min": 0,
                "max": 23
            },
            "order": {
                "_key": "desc"
            }
        }
    }

This is nice, as it'll also include any hours with zero results, and, it'll extend the results to cover the entire 24 hour period (due to the extended_bounds).

You can use 'getDayOfWeek', 'getHourOfDay', ... (see 'Joda time' for more).

This is great for hours, but for days/months it'll give you a number rather than the month name. To work around, you can get the timeslot as a string - but, this won't work with the extended bounds approach, so you may have empty results (i.e. [Mon, Tues, Fri, Sun]).

In-case you want that, it is here:

"aggregations": {
    "dayOfWeek": {
        "terms": {
            "script": "doc['timestamp'].value.getDayOfWeek().getAsText()",
            "order": {
                "_term": "asc"
            }
        }
    }

Even if this doesn't help you, hopefully someone else will find it and benefit from it.

Hug

for Elastic 7+, be sure to use getDayOfWeekEnum() – Daric

Answer 3

The simplest way would be to define a dedicated day-of-week field that holds only the day of the week for each document, then do a terms aggregation on that field.

If for whatever reason you don't want to do that (or can't), here is a hack that might help you get what you want. The basic idea is to define a "date.raw" sub-field that is a string, analyzed with the standard analyzer so that terms are created for each day of the week. Then you can aggregate on those terms to get your counts, using include to only include the terms you want.

Here is the mapping I used for testing:

PUT /test_index
{
   "settings": {
      "number_of_shards": 1
   },
   "mappings": {
      "doc": {
         "properties": {
            "msg": {
               "type": "string"
            },
            "date": {
               "type": "date",
               "format": "E, dd MMM yyyy",
               "fields": {
                  "raw": {
                     "type": "string"
                  }
               }
            }
         }
      }
   }
}

and a few sample docs:

POST /test_index/_bulk
{"index":{"_index":"test_index","_type":"doc","_id":1}}
{"msg": "hello","date": "Wed, 11 Mar 2015"}
{"index":{"_index":"test_index","_type":"doc","_id":2}}
{"msg": "hello","date": "Tue, 10 Mar 2015"}
{"index":{"_index":"test_index","_type":"doc","_id":3}}
{"msg": "hello","date": "Mon, 09 Mar 2015"}
{"index":{"_index":"test_index","_type":"doc","_id":4}}
{"msg": "hello","date": "Wed, 04 Mar 2015"}

and the aggregation and results:

POST /test_index/_search?search_type=count
{
    "aggs":{
        "docs_by_day":{
            "terms":{
                "field": "date.raw",
                "include": "mon|tue|wed|thu|fri|sat|sun"
            }
        }
    }
}
...
{
   "took": 2,
   "timed_out": false,
   "_shards": {
      "total": 1,
      "successful": 1,
      "failed": 0
   },
   "hits": {
      "total": 4,
      "max_score": 0,
      "hits": []
   },
   "aggregations": {
      "docs_by_day": {
         "buckets": [
            {
               "key": "wed",
               "doc_count": 2
            },
            {
               "key": "mon",
               "doc_count": 1
            },
            {
               "key": "tue",
               "doc_count": 1
            }
         ]
      }
   }
}

Here is the code all together:

http://sense.qbox.io/gist/0292ddf8a97b2d96bd234b787c7863a4bffb14c5

Newsman