Week of the year aggregations using Painless

Question

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

Answer 1

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);