# Week of the year aggregations using Painless

**URL:** <https://discuss.elastic.co/t/week-of-the-year-aggregations-using-painless/140071>\
**Category:** Elasticsearch\
**Created:** [July 16, 2018, 2:11am UTC](https://discuss.elastic.co/t/week-of-the-year-aggregations-using-painless/140071 "2018-07-16T02:11:02Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Paulo\_Silva](https://avatars.discourse-cdn.com/v4/letter/p/dec6dc/32.png) [@Paulo\_Silva](https://discuss.elastic.co/u/Paulo_Silva)\
**Post date:** [July 16, 2018, 2:11am UTC](https://discuss.elastic.co/t/week-of-the-year-aggregations-using-painless/140071/1 "2018-07-16T02:11:02Z")

</div>

Hi guys,

I'm facing a challenge while aggregating data by the "Week of Year" number.

**The use case is:**  
The first day of the week is set as Sunday on my PC.  
create a data range: start=' **2018-06-24** Sunday' and end=' **2018-06-30** Saturday'  
Expected: I can only see one week aggregated  
Actual: I can see two weeks aggregated

**The Elastic Query:**

```
{
  "size": 0,
  "aggs": {
    "groupby": {
      "terms": {
        "script": {
          "source": "ZonedDateTime.ofInstant(Instant.ofEpochMilli(doc['CLOSED_DATE'].value.millis), ZoneId.of('UTC')).get(IsoFields.WEEK_OF_WEEK_BASED_YEAR)"
        },
        "size": 100
      }
    }
  },
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "CLOSED_DATE": {
              "gte": "2018-06-24T00:00:01",
              "lte": "2018-06-30T23:59:59",
              "time_zone": "UTC"
            }
          }
        }
      ]
    }
  }
}

```

I also tried this, and got the same results:

```
{
  "size": 0,
  "aggs": {
    "groupby": {
      "terms": {
        "script": {
          "source": "doc['CLOSED_DATE'].value.getWeekOfWeekyear()"
        },
        "size": 100
      }
    }
  },
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "CLOSED_DATE": {
              "gte": "2018-06-24T00:00:01",
              "lte": "2018-06-30T23:59:59",
              "time_zone": "UTC"
            }
          }
        }
      ]
    }
  }
}

```

To be fair, this query works alright for most use cases.  
It's failing is scenarios like this use case above.

Any tip will be welcome!!!  
Thanks

---

<div class="post-metadata">

**Author:** ![Jack\_Conradson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jack_conradson/32/47236_2.png) [@Jack\_Conradson](https://discuss.elastic.co/u/Jack_Conradson)\
**Post date:** [July 20, 2018, 4:33pm UTC](https://discuss.elastic.co/t/week-of-the-year-aggregations-using-painless/140071/2 "2018-07-20T16:33:18Z")

</div>

Based on ISO 8601 it looks like weeks run from Monday through Sunday and not Sunday through Saturday. I'm not sure how you're setting first day of week on your PC, but if you do a week as Monday through Sunday instead, does this behave as expected?

---

<div class="post-metadata">

**Author:** ![Paulo\_Silva](https://avatars.discourse-cdn.com/v4/letter/p/dec6dc/32.png) [@Paulo\_Silva](https://discuss.elastic.co/u/Paulo_Silva)\
**Post date:** [August 6, 2018, 12:18am UTC](https://discuss.elastic.co/t/week-of-the-year-aggregations-using-painless/140071/3 "2018-08-06T00:18:15Z")

</div>

Hi,

This did the trick:  
`def date = ZonedDateTime.ofInstant(Instant.ofEpochMilli(doc[params.dt_field_name].value), ZoneId.of(params.timezone)); DayOfWeek dayOfWeek = DayOfWeek.valueOf(params.first_day_week); date = date.with(TemporalAdjusters.previousOrSame(dayOfWeek)).plusWeeks(1); return date.get(IsoFields.WEEK_OF_WEEK_BASED_YEAR);`

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [August 6, 2018, 5:01am UTC](https://discuss.elastic.co/t/week-of-the-year-aggregations-using-painless/140071/4 "2018-08-06T05:01:46Z")

</div>

Thanks for sharing your solution.  
That'd be definitely better to compute that at index time rather than at query time IMO.

---

<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:** [September 3, 2018, 5:01am UTC](https://discuss.elastic.co/t/week-of-the-year-aggregations-using-painless/140071/5 "2018-09-03T05:01:50Z")

</div>

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