date intervals with php and mysql

[Kultsound]

Hi all!

I'm trying to find a quick and elegant way to compare two date intervals, however these overlap or not. I have defined two sets of variables for projects;

$date_a and $date_b represent start and end of one project
$date_y and $date_z represent dito for the second project

To find out however the two DO NOT overlap is easy using a MySQL statement as follows;

($date_a < $date_y AND $date_b < $date_y) OR ($date_a > $date_z AND $date_b > $date_z)

or in other words interval A-B is on either side of interval Y-Z

But, I need to know however the two overlap!! There would be 12 pairs to compare against each oher to explicitly find out however that's the case and it doesn't seem to me like that is a very elegant solution... Is there a set of PHP functions that do it? Any ideas????

[Sxooter]

Are you looking for how much they overlap? I.e. days / hours kinda output? Or just if they overlap? Or if one is contained in another or vice versa?

It's no big deal to programmatically create a rather large, ungainly looking SQL query and toss it at the database. It should still be pretty fast.

It's just a shame that MySQL doesn't have an interval type. Would make this kinda thing a little easier...

[Sxooter]

Hey, do you know if MySQL supports the ANSI SQL overlaps operator? It works like this:

select ( start1, end1 ) OVERLAPS ( start2, end2 )

will return true if they do overlap, and false if they don't