views:

33

answers:

1

I am working on a mysql query that will search a database of entries that each have a time span to see if a user input time or time span falls within the entries spans.

Ex user can input "13:00" or "13:00-16:00"
the database has entries like so:

id    startTime    endTime  
1     08:00        12:00  
2     20:00        03:00  
3     14:00        01:00  
4     16:00        21:00  

Searching is easy enough against a time span that is during a single day (startTime < endTime). The issue is testing when a span goes across midnight (endTime < startTime). For this application these values can not have a date attachment (they are stored as time only), so timediff etc will not work.

UPDATE:
The time spans will never be greater than 24 hrs so startTime of 1 and endTime of 3 will always be a 2 hr item, not a 26 hr item.

The reason I am not using dates is this relates to something more or less like business hours (but not exactly that use case). The items the spans represent have daily hours, so it is not practical to add datetime stamps for every day.

+3  A: 
Kyra
Another reason to store dates: what happens if the range includes a switch in Daylight Savings Time? `01:59 - 02:01` might be two minutes, or it might be one hour and two minutes.
Marcus Adams
See above on why we do not have dates stored. Daylight savings is not a big deal as we are not calculating the duration, just trying to see if a time is within the span.This answer works ok on items that do span midnight, but produces incorrect results on a normal span. To selectively apply it would require more logic in SQL than I would like...
Thildemar
You just have to do an if else statement. If endTime>startTime then do above answer, else do what you are doing now. Unless you come across anything better this is all I can think of now.
Kyra