Time compare problems

[Dillenger]

Hello I am trying to show only restaurants that are open, which is fine, I use this:

hours.hour1 < '$timecompare' AND hours.hour2 > '$timecompare'

The problem is sometimes a restaurant is open until 2AM, so in the hour2 field it says 02:00:00, but that breaks the search because it's esentially looking only at today, which is only a 24 hour period.

Is there any way around this, or to better structure my statement?

[Sxooter]

Pick an arbitrary date, like Jan 1 2000, and add / subtract the time from that. If the closing time is before the opening time then add it and a day to the date, otherwise just add the time to the arbitrary date.

[Dillenger]

Or I guess I could just say (if hour2. < hour1) add a day to hour2? Both these solutions would require me to do a call to the database before hand though, I was hoping to do it right in the mysql call.

[HalfaBee]

Have a look at the mysql CASE statement.