Return date_histogram in hour for each day

[ysl]

Hello,

I have records with 15m interval timestamp.

Now I want to return with:

timestamps grouped by 60 minutes --> date_histogram aggregation with interval at 60m (4 docs in one bucket, 15m per record)
return the sum of screening_time for hourly --> sum aggregation on the screening_time.
which hour has the highest screening_time --> max bucket (24 buckets a day)

Now I have the max bucket up to one day, it comes to an issue if I have 7 days of data, how do I return the highest value screening_time for that particular hour each of the day ?

This is my code:

GET test/_search
{
"size": 0,
"query": {
"bool": {
"filter": [
{ "match": { "category": "subset" } }
]
}
},
"aggs" : {
"screening_time" : {
"date_histogram" : {
"field" : "timestamp",
"interval" : "60m"
},
"aggs": {
"sales": {
"sum": {
"field": "total_sales"
}
}
}
},
"max_monthly_sales": {
"max_bucket": {
"buckets_path": "screening_time>sales"
}
}
}
}

[dadoonet (David Pilato)]

Not sure but it'd be probably easier if you index that value (hour of the day) in a field at index time.

Otherwise you can probably use https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html to compute the hour of the day but I guess it will be slower to execute.