[RESOLVED] Finding overlapping timestamp intervals

david8787

I have a database of time intervals which has 2 columns (startTime, endTime) which are stored as unix timestamps in an int field type.

In my php script I have another set of intervals of unix timestamps and I only want to get a set of intervals that overlap.

I've tried the following but it doesn't seem to work.

$query = "SELECT * FROM timeslots WHERE startTime <= $endTime AND $startTime <= endTime";

This is the unterval in my database🙁1270832400, 1270836000)
This is the interval from my script: (1270832400, 1270839600)

Any ideas?

bradgrafelman

Why integers? Why not use the DATE column type? Try something like the following:

...WHERE ('$startTime' BETWEEN startTime AND endTime) OR ('$endTime' BETWEEN startTime AND endTime)

david8787

Ok that did the trick. Should I change the field to datetime and work with that instead?

bradgrafelman

david8787;10948993 wrote:
Ok that did the trick. Should I change the field to datetime and work with that instead?

Either DATE or DATETIME (depending upon whether or not you care about the "time" component as well) are, IMHO, preferable when storing dates and times in a database.

Doing so allows you to use, for example, MySQL's Date and Time Functions (there's quite a few, as you can see) to easily/properly manipulate this type of data.

david8787

Thanks for the help.