Average per day of week with time_zone

Elastic Stack Elasticsearch
1

Hello,

I have a message index with a 'date' and 'channelId' fields:
POST message/_doc
{
"channelId": "channel_twitter",
"date": "2018-03-08T14:01:23.385Z"
}

I need to create an aggregation that calculates the average number of messages per day of the week per channel. For example - for twitter - an average of 5 messages on Mondays, 10 messages on Tuesdays etc.
So far I have managed to get this working but now need to make it accept a timezone offset ( "time_zone":" [(${timezoneOffset})]") parameter which will return the correct amount of messages for each day depending on time_zone.

{
"size": 0,
"aggs": {
"messages_per_channel": {
"terms": {
"field": "channelId"
},
"aggs": {
"total_messages_per_day_of_week": {
"terms": {
"script": {
"lang": "painless",
"source": "doc['date'].value.dayOfWeek"
}
},
"aggs": {
"number_of_weeks": {
"date_histogram": {
"field": "date",
"interval": "week",
"extended_bounds" : {
"max" : "now+1m/m"
}
}
},
"average_messages_per_day_of_week": {
"bucket_script": {
"buckets_path": {
"doc_count": "_count",
"number_of_weeks": "number_of_weeks._bucket_count"
},
"script": "params.doc_count / params.number_of_weeks"
}
}
}
}
}
}
}
}

All my attempts have ended in errors so any help would be appreciated.

2

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