# Filter date histogram buckets
**URL:** https://discuss.elastic.co/t/filter-date-histogram-buckets/353623
**Category:** Elasticsearch
**Created:** [February 19, 2024, 3:31pm UTC](https://discuss.elastic.co/t/filter-date-histogram-buckets/353623 "2024-02-19T15:31:13Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@mwitsas](https://discuss.elastic.co/u/mwitsas)
#### Post date: [February 19, 2024, 3:31pm UTC](https://discuss.elastic.co/t/filter-date-histogram-buckets/353623/1 "2024-02-19T15:31:13Z")
Could anyone help me to understand how to filter the following query just to return minute buckets containing zero documents? Equivalent of a GROUP BY with a HAVING statement in SQL. My aim is to return a list of minutes which contain no documents during the day.
```auto
{
"size": 0,
"query": {
"range": {
"@timestamp": {
"gte": "2024-02-18T00:00:00.000Z",
"lte": "2024-02-18T23:59:59.999Z"
}
}
},
"aggs": {
"count_per_minute": {
"date_histogram": {
"field": "@timestamp",
"interval": "minute",
"order": {
"_key": "asc"
}
}
}
}
}
```
---
### Author:  [@Abinash\_Raja](https://discuss.elastic.co/u/Abinash_Raja)
#### Post date: [February 20, 2024, 7:13am UTC](https://discuss.elastic.co/t/filter-date-histogram-buckets/353623/2 "2024-02-20T07:13:33Z")
Hi @mwitsas,
> how to filter the following query just to return minute buckets containing zero documents?
You can achieve the same using the aggregations **Value count** and **`bucket selector`** with `gap_policy` set as `insert_zeros`
Sample Query:
```auto
{
"size": 0,
"query": {
"range": {
"@timestamp": {
"gte": "2024-02-18T00:00:00.000Z",
"lte": "2024-02-18T23:59:59.999Z"
}
}
},
"aggs": {
"count_per_minute": {
"date_histogram": {
"field": "@timestamp",
"interval": "minute",
"order": {
"_key": "asc"
}
},
"aggregations": {
"bucket_filter": {
"bucket_selector": {
"gap_policy": "insert_zeros",
"buckets_path": {
"ags": "timestamp_value_count"
},
"script": {
"source": "params.ags == 0",
"lang": "painless"
}
}
},
"timestamp_value_count": {
"value_count": {
"field": "@timestamp"
}
}
}
}
}
}
```
---
### Author:  [@Abinash\_Raja](https://discuss.elastic.co/u/Abinash_Raja)
#### Post date: [February 20, 2024, 10:09am UTC](https://discuss.elastic.co/t/filter-date-histogram-buckets/353623/3 "2024-02-20T10:09:02Z")
It can also be achieved by using **`bucket selector`** aggregation alone.
Sample Query:
```auto
{
"size": 0,
"query": {
"range": {
"@timestamp": {
"gte": "2024-02-18T00:00:00.000Z",
"lte": "2024-02-18T23:59:59.999Z"
}
}
},
"aggs": {
"count_per_minute": {
"date_histogram": {
"field": "@timestamp",
"interval": "minute",
"order": {
"_key": "asc"
}
},
"aggregations": {
"bucket_filter": {
"bucket_selector": {
"gap_policy": "insert_zeros",
"buckets_path": {
"ags": "_count"
},
"script": {
"source": "params.ags == 0",
"lang": "painless"
}
}
}
}
}
}
}
```
---
### Author:  [@mwitsas](https://discuss.elastic.co/u/mwitsas)
#### Post date: [February 21, 2024, 12:32pm UTC](https://discuss.elastic.co/t/filter-date-histogram-buckets/353623/4 "2024-02-21T12:32:38Z")
Many thanks - this did exactly what I needed
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [March 20, 2024, 12:33pm UTC](https://discuss.elastic.co/t/filter-date-histogram-buckets/353623/5 "2024-03-20T12:33:29Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.