Week of the year aggregations using Painless

Elastic Stack Elasticsearch
1

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

2

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?

3

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

4

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

5

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