6

I have been trying to use date histogram aggregation in elasticsearch and it returns the date as epoch or in yy-mm-dd-mm-ss format. But what I want is to get document count per week day like monday , tuesday etc. Is there any way in which I can do that?

Simi
  • 117
  • 1
  • 4

3 Answers3

8

I may be missing something, but, isn't the answer simpler than Vineeth's answer?

"aggregations": {
    "timeslice": {
        "histogram": {
            "script": "doc['timestamp'].date.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 an number rather than the month name. To work around, you can get the timeslot as a string - but, this'll 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'].date.dayOfWeek().getAsText()",
            "order": {
                "_term": "asc"
            }
        }
    }

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

RichS
  • 3,097
  • 30
  • 26
  • 3
    This worked for me but I had to use this format for the script: `"script": "doc['timestamp'].date.dayOfWeek"` Notice the missing parenthesis. – Derek Sep 29 '16 at 15:36
  • 3
    This works well. The new syntax for Elastic 6.6 for getting the hour of the day is: `doc['timestamp'].value.getHour()`. Elastic already knows that `timestamp` is a date, so you can use `.value` instead of the `date` function. – jasonrhaas Mar 21 '19 at 23:40
4

You need to go for a different approach. Using scripts , you can convert date time into week day. On this value if you apply terms aggregation , it should work fine.

Script to convert date time value into weekday

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

Query to get the values

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

You can also find some more examples on using scripting in aggregations here.

Vineeth Mohan
  • 18,633
  • 8
  • 63
  • 77
2

For ES5 using painless, this works:

"aggs": {
    "dayOfWeek": {
        "terms": {
            "script": {
                "inline": "doc['date_utc'].date.dayOfWeek",
                "lang": "painless"
            }
        }
    }
}
GTavasoli
  • 448
  • 4
  • 11
Oliver Lloyd
  • 4,936
  • 7
  • 33
  • 55
  • hi, can you tell me how can we get the day of week in text format like Monday, Tuesday etc. – pravindot17 Aug 24 '17 at 11:10
  • http://www.joda.org/joda-time/key_format.html -> a script containing e.g. "doc['timestamp'].date.toString('E')" would output Mon, Tue, Wed ... – David Goodwin Sep 03 '18 at 13:42
  • You can use e.g. "Instant.ofEpochMilli(doc.timestamp.date.millis).atZone(ZoneId.of(params.tz)).format(DateTimeFormatter.ofPattern('e HH'))" and pass in any pattern from https://docs.oracle.com/javase/9/docs/api/java/time/format/DateTimeFormatter.html#patterns (This gives you timezone support ... etc) – David Goodwin Sep 03 '18 at 15:48
  • this changes by the time you get to ES7 - to something closer to : Instant.ofEpochMilli(doc.timestamp.getValue().getMillis()).atZone(ZoneId.of(params.tz)).format(DateTimeFormatter.ofPattern('E HH')) – David Goodwin Apr 18 '23 at 15:57