Date Histogram - Day of Week aggregation

[Paulo_Henrique_PH]

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.

[spinscale (Alexander Reelsen)]

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

[Paulo_Henrique_PH]

Hi @spinscale!

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

Cheers

[spinscale (Alexander Reelsen)]

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

[Paulo_Henrique_PH]

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

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); ", " ^---- H…

No luck unforcefully...

[spinscale (Alexander Reelsen)]

Hey,

if it helps, you can add hours via

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

--Alex

[Paulo_Henrique_PH]

Looks promissing indeed!
I will try that out
Thanks!

[Paulo_Henrique_PH]

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