I have the following table in MySQL: data(interval_id, object_id, start, end), where interval_id, object_id are integers and interval_id is unique per every object_id, while object_id itself is unique. start, end are timestamps(and end can be NULL).

Say that I want to eliminate overlapping intervals but if there's any, I want to keep the ones with the highest interval_id.

For the interval elimination, I am using the following criteria:

(t1.start <=t2.end or t2.end IS NULL) AND (t1.end>=t2.start OR t1.end IS NULL)

I am doing a (self) CROSS JOIN and have something like this already:

SELECT t1.object_id, t1.interval_id, t1.start, t1.stop
FROM data AS t1, data AS t2
WHERE t1.object_id = t2.object_id -- we want to compare same objects
AND (t1.start <=t2.end or t2.end IS NULL) AND (t1.end>=t2.start OR t1.end IS NULL)) --overlapping condition
AND t1.interval_id > t2.interval_id --keep ones with the highest id?

My main question is if this is a correct approach to keep both non-overlapping intervals and overlapping intervals with the highest interval_id or am I doing anything wrong? I wish I could fully test out myself but I currently don't have access to the db, so any input would be appreciated.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.