# Overlapping date range

**URL:** <https://discuss.elastic.co/t/overlapping-date-range/29105>\
**Category:** Elasticsearch\
**Created:** [September 11, 2015, 8:44am UTC](https://discuss.elastic.co/t/overlapping-date-range/29105 "2015-09-11T08:44:06Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![td583](https://avatars.discourse-cdn.com/v4/letter/t/c77e96/32.png) [@td583](https://discuss.elastic.co/u/td583)\
**Post date:** [September 11, 2015, 8:44am UTC](https://discuss.elastic.co/t/overlapping-date-range/29105/1 "2015-09-11T08:44:06Z")

</div>

Hi

I want to retrieve overlapping time ranges for every product\_id. Product\_id's have multiple time ranges (every status change is a new record, so also a new time range). I've already tried it in MySQL and the query looks like this:

select s1.id, s1.product\_id, s1.from\_date, s1.until\_date, s2.id, s2.product\_id, s2.from\_date, s2.until\_date, s1.status, s2.status  
from status\_history s1  
inner join status\_history s2  
on s1.product\_id = s2.product\_id  
where s1.id \<\> s2.id  
AND s2.id \> s1.id  
AND (s1.until\_date \> s2.from\_date)

What's the best way to achieve this in elasticsearch?  
I thought about aggregations to get every status change (and time range) for each product\_id. But apparently these only return document counts.

Thanks in advance!

---

<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:** [July 5, 2017, 11:50pm UTC](https://discuss.elastic.co/t/overlapping-date-range/29105/2 "2017-07-05T23:50:52Z")

</div>