comparing time ranges

Question

hi! I'm trying to write an sql statement which compares time ranges. I got some excellent ideas from this question:

http://stackoverflow.com/questions/143552/comparing-date-ranges

the problem however is times that span midnight e.g. given the following start and end times:

• 19:00 - 20:00
• 21:00 - 21:30
• 23:00 - 01:00
• 01:00 - 03:00

I want to find any rows where the times intersect/overlap 23:30 - 00:30. The code I have only works if both the rows in the db and the time range I want to check start and end on the same day.

I've tried converting the times to seconds since midnight and then adding 86400 seconds to the end time if the range spans midnight. I've had limited success with that but it still misses some overlapping ranges.