CODEWITHSUNDEEP
phpmysqldatetime
DevMan
DevMan

Reputation: 556

How to find time intersection between two times stored in mysql database

The problem I'm facing is not very complicated and there are several answers to this here, but I didn't see any which is solving the problem as I need. So I have a table with two fields of type VARCHAR, named timefrom and timeto. Those fields store time in this format: 12:00, 05:18,00:05 .... I have no date and no seconds, just hour and minutes in 24/h time format. I have a form with two fields, starttime and endtime. And now I want to select all rows from database where the user starttime and endtime intersects with the timefrom and timeto fields in DB. I have the following query:

"SELECT * FROM reservation WHERE ((timeto>='$timefrom' AND timeto<='$timeto') OR (timefrom>='$timefrom' AND timefrom <='$timeto'))"

This query works for all kind of intersections but not if the time given by user fits in the time stored in DB. For example if DB timefrom=20:00 and timeto=23:00 and the user input starttime=21:00 and endtime=22:00 the query doesn't return any intersection and it should. It would be a great help if anyone could show me a reliable solution. THANKS

Upvotes: 1

Views: 1555

Answers (3)

Oliver Maksimovic
Oliver Maksimovic

Reputation: 3262

Saving any sort of date/time as a simple string is not a good idea because you lose out-of-box features for working with dates & time provided by MySQL itself.

Try something like this:

SELECT * FROM reservations
WHERE
TIME_FORMAT(timefrom, '%H:%i') >= '21:00' AND TIME_FORMAT(timeto, '%H:%i') <= '23:00'

Upvotes: 2

imran arshad
imran arshad

Reputation: 274

Why not just check that either one of the user input starttime or user input endtime is in between the time limits. And whether DB starttime and endtime are both between user starttime and endtime. That would mean an intersection.

SELECT * FROM reservation WHERE ('$timefrom' BETWEEN timeto AND timefrom) OR ('$timeto' BETWEEN timeto AND timefrom) OR ('$timefrom' <= timefrom AND '$timeto' >= timeto)

Upvotes: 1

Otterbein
Otterbein

Reputation: 544

Well you could just add a third check if the user-Time is between the timeto and timefrom:

OR (timefrom <= '$timefrom' AND timeto >='$timeto')

This would spit you out the rows with the times in between too.

MySQL provides an intersect filter too. Look at this. There you can provde the rows to look at and then say that you just want to select the rows which provides an intersection with your values.

Upvotes: 0

Related Questions