# Find documents with multiple occurrences within a time span

**URL:** <https://discuss.elastic.co/t/find-documents-with-multiple-occurrences-within-a-time-span/118608>\
**Category:** Elasticsearch\
**Created:** [February 6, 2018, 10:16am UTC](https://discuss.elastic.co/t/find-documents-with-multiple-occurrences-within-a-time-span/118608 "2018-02-06T10:16:43Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![adammichalik](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/adammichalik/32/20422_2.png) [@adammichalik](https://discuss.elastic.co/u/adammichalik)\
**Post date:** [February 6, 2018, 10:16am UTC](https://discuss.elastic.co/t/find-documents-with-multiple-occurrences-within-a-time-span/118608/1 "2018-02-06T10:16:43Z")

</div>

I have documents with a following structure:

```
{
  "timestamp": "2018-01-01T12:34:56.000Z",
  "message": "Some message",
  "idNumber": 12345678
}

```

The `message` field holds various messages, there are multiple messages per `idNumber`.  
I would like to find the `idNumber`s for which there are two or more documents with `message` equal to `"Hello"` within one hour. How can I achieve that (if it's possible at all)?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [February 6, 2018, 4:29pm UTC](https://discuss.elastic.co/t/find-documents-with-multiple-occurrences-within-a-time-span/118608/2 "2018-02-06T16:29:29Z")

</div>

This feels a little hacky but works on a small set of data:

```
PUT test
{
  "settings": {
	"number_of_replicas": 0,
	"number_of_shards": 1
  },
  "mappings": {
	"doc":{
	  "properties": {
		"date":{
		  "type":"date",
		  "format":"yyyy-MM-dd HH:mm"
		},
		"id":{
		  "type":"keyword"
		},
		"message":{
		  "type":"text"
		}
	  }
	}
  }
}
POST test/doc/_bulk
{"index":{}}
{"date":"2018-01-01 01:01", "id":"bad","message":"hello"}
{"index":{}}
{"date":"2018-01-01 01:02", "id":"bad","message":"hello"}
{"index":{}}
{"date":"2018-01-01 01:02", "id":"good","message":"hello"}
{"index":{}}
{"date":"2018-01-01 03:02", "id":"good","message":"hello"}

GET test/_search?q=hello
{
  "size": 0,
  "aggs": {
	"terms": {
	  "terms": {
		"min_doc_count": 2,
		"shard_min_doc_count": 1,
		"script": {
		  "source": """
 doc['date'].value.getHourOfDay().toString() + "-"+
 doc['date'].value.getDayOfMonth().toString()+ "-"+ 
 doc['date'].value.getMonthOfYear().toString()+ "-"+
 doc['date'].value.getYear().toString()+ "-"+
 doc["id"].value
"""
		}
	  }
	}
  }
}  

```

I'm sure there's a better way of formatting the date but essentially I combine the ID and a string representation of an hour-level bucket. Obviously this doesn't spot 2 events close to an hour boundary e.g. 12:59 and 13:01 but might be good enough. If you have many unique IDs and they are spread across multiple shards then this will not scale and you need to think about indexing approaches that bring related data closer together.

---

<div class="post-metadata">

**Author:** ![adammichalik](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/adammichalik/32/20422_2.png) [@adammichalik](https://discuss.elastic.co/u/adammichalik)\
**Post date:** [February 7, 2018, 1:37pm UTC](https://discuss.elastic.co/t/find-documents-with-multiple-occurrences-within-a-time-span/118608/3 "2018-02-07T13:37:42Z")

</div>

Thanks, Mark. This is for reporting purposes, so speed is not so much of a requirement and the results can be approximate as long as I can get a good sample of occurrences. I'll try that out.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 7, 2018, 1:37pm UTC](https://discuss.elastic.co/t/find-documents-with-multiple-occurrences-within-a-time-span/118608/4 "2018-03-07T13:37:56Z")

</div>

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