This seemed like it should have been simple, but turns out it's not (to my anyway).
I'm trying to select records that have a time of day that falls between two other times. On the site, basically events are added that happened at a certain time of day. The user can then select a time range to see all events that fall between those times of the day.
Let's say I have a row with a time = '09:00'
SELECT * FROM table WHERE time BETWEEN '08:00' AND '10:30'
then returns the row.
Now let's say I have a row with time = '00:00' (midnight)
SELECT * FROM table WHERE time BETWEEN '23:00' AND '01:30'
then does not return the row.
Now, logically, midnight is between 11:00 PM and 01:30 AM, but the commands assume you're talking about times on the same day, so you don't get the right results.
How do I overcome this? I'm starting to think MySQL isn't going to allow it. I'm using PHP with MySQL, any function I can use?
Thanks in advance for some help!