CODEWITHSUNDEEP
mysqlsqltimetimespan
Thildemar
Thildemar

Reputation: 99

mysql test if time is within a time span over midnight

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.

Upvotes: 2

Views: 2253

Answers (1)

Kyra
Kyra

Reputation: 5407

I would check if endTime

  1. totaltime = startTime + (24 hours - endTime)

The problem I see with this is if the endTime is the next day and endTime>startTime. For example you start at noon and end at 1pm the next day. There is also a problem if the time spans more than 2 days. For example start on day 1 and end day 3 or longer. I would recommend using dates.

Upvotes: 3

Related Questions