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!