DevMan DevMan - 2 years ago 141
MySQL Question

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

Answer Source

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
TIME_FORMAT(timefrom, '%H:%i') >= '21:00' AND TIME_FORMAT(timeto, '%H:%i') <= '23:00'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download