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.