# Date Histogram - Day of Week aggregation

**URL:** <https://discuss.elastic.co/t/date-histogram-day-of-week-aggregation/81740>\
**Category:** Elasticsearch\
**Created:** [April 10, 2017, 4:08am UTC](https://discuss.elastic.co/t/date-histogram-day-of-week-aggregation/81740 "2017-04-10T04:08:20Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Paulo\_Henrique\_PH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/paulo_henrique_ph/32/14831_2.png) [@Paulo\_Henrique\_PH](https://discuss.elastic.co/u/Paulo_Henrique_PH)\
**Post date:** [April 10, 2017, 4:08am UTC](https://discuss.elastic.co/t/date-histogram-day-of-week-aggregation/81740/1 "2017-04-10T04:08:20Z")

</div>

Hi all,

I know the only way to achieve it in old releases was through scripting.

I'm wondering if that limitation was overcome in the latest release and potentially we could you the standard command, indicating the correct format/interval (i.e):

```
  "aggs": {
    "groupby": {
      "date_histogram": {
        "field": "MODIFIED_DATE",
        "interval": " **_dayOfWeek_**",
        "format": " **_weekDay_**",
        "min_doc_count": 1,
        "time_zone": "-03:00"
      },

```

Thanks guys.

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [April 10, 2017, 9:52am UTC](https://discuss.elastic.co/t/date-histogram-day-of-week-aggregation/81740/2 "2017-04-10T09:52:58Z")

</div>

Hey,

scripting is one way to go. But you could also use the reindex API and add the day of the week in its own field and then run a terms aggregation on that - which would be waaaay faster than the scripting solution.

--Alex

---

<div class="post-metadata">

**Author:** ![Paulo\_Henrique\_PH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/paulo_henrique_ph/32/14831_2.png) [@Paulo\_Henrique\_PH](https://discuss.elastic.co/u/Paulo_Henrique_PH)\
**Post date:** [April 11, 2017, 1:20am UTC](https://discuss.elastic.co/t/date-histogram-day-of-week-aggregation/81740/3 "2017-04-11T01:20:34Z")

</div>

Hi @spinscale!

The problem with that approach is the time zone offset we need here.

Cheers

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [April 11, 2017, 7:17am UTC](https://discuss.elastic.co/t/date-histogram-day-of-week-aggregation/81740/4 "2017-04-11T07:17:51Z")

</div>

Is there any possibility to factor that in as well during the reindex operation (maybe via a second field in addition to the day of the week add the timezone and base your queries on that)?

---

<div class="post-metadata">

**Author:** ![Paulo\_Henrique\_PH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/paulo_henrique_ph/32/14831_2.png) [@Paulo\_Henrique\_PH](https://discuss.elastic.co/u/Paulo_Henrique_PH)\
**Post date:** [April 11, 2017, 7:21am UTC](https://discuss.elastic.co/t/date-histogram-day-of-week-aggregation/81740/5 "2017-04-11T07:21:40Z")

</div>

Hi @spinscale  
Thanks for the reply.

Only if we index all the time zone available... not feasible in this scenario.  
We've got it up running for other date formats (year, month and year etc)

I've tried this approach here:

> [@Day of Week aggregation with TimeZone](https://discuss.elastic.co/t/day-of-week-aggregation-with-timezone/81924):
>
> I use ES 5.1.2 and I'm trying to compute day of week and time of day from a date field and consider timezone at the same time. my first script is def d = doc['my\_field'].date; d.addHours(10); d.getDayOfWeek(); The error message is can't find addHours() method "caused\_by": { "type": "illegal\_argument\_exception", "reason": "Unable to find dynamic method [addHours] with [1] arguments for class [org.joda.time.MutableDateTime]." }, "script\_stack": [ "d.addHours(10); ", " ^---- HERE…

No luck unforcefully...

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [April 11, 2017, 7:57am UTC](https://discuss.elastic.co/t/date-histogram-day-of-week-aggregation/81740/6 "2017-04-11T07:57:55Z")

</div>

Hey,

if it helps, you can add hours via

`Instant.ofEpochMilli(doc['time'].value).plus(Duration.ofHours(5))`

--Alex

---

<div class="post-metadata">

**Author:** ![Paulo\_Henrique\_PH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/paulo_henrique_ph/32/14831_2.png) [@Paulo\_Henrique\_PH](https://discuss.elastic.co/u/Paulo_Henrique_PH)\
**Post date:** [April 11, 2017, 8:02am UTC](https://discuss.elastic.co/t/date-histogram-day-of-week-aggregation/81740/7 "2017-04-11T08:02:48Z")

</div>

Looks promissing indeed!  
I will try that out 🙂  
Thanks!

---

<div class="post-metadata">

**Author:** ![Paulo\_Henrique\_PH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/paulo_henrique_ph/32/14831_2.png) [@Paulo\_Henrique\_PH](https://discuss.elastic.co/u/Paulo_Henrique_PH)\
**Post date:** [April 11, 2017, 8:09am UTC](https://discuss.elastic.co/t/date-histogram-day-of-week-aggregation/81740/8 "2017-04-11T08:09:05Z")

</div>

HI @spinscale

Results are matching, thanks!

I guess the final question is:  
How can I format it in order to get something like '`doc['field_name'].date.dayOfWeek`' ?

Thanks once again

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 9, 2017, 8:19am UTC](https://discuss.elastic.co/t/date-histogram-day-of-week-aggregation/81740/9 "2017-05-09T08:19:13Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.