MySQL time between days

Question

I have an application storing the times a restaurant is open:

My code to get the currently open restaurants. My SQL query (simplified) looks like

SELECT * 
  FROM `restaurants` r 
 WHERE r.from <= NOW()  
   AND r.to >= NOW();

The problem here is, there's an entry which rolls over -- it's for a restaurant open from 11 AM to 3 AM the next day.

What would be a good query to capture that particular restaurant?

Answer 1

In pseudo code:

if (close > open) {
    store_is_open = (open <= now <= close)
} else {
    store_is_open = (open <= now || now <= close)
}

Converting that to SQL:

WHERE IF(
    r.from < r.to,
    NOW() BETWEEN r.from AND r.to,
    NOW() >= r.from OR NOW() <= r.to
)

You might also want to check for places which are open 24 hours. The below code assumes that you would set the from and to times to be the same (eg: from midnight to midnight)

WHERE IF(
    r.from = r.to,
    1,
    IF(
        r.from < r.to,
        NOW() BETWEEN r.from AND r.to,
        NOW() >= r.from OR NOW() <= r.to
    )
)

Answer 2

i understand from the question, that the dates is save in DB in datetime type,(you use NOW() function)

so try to use :

SELECT * FROM `restaurants` r WHERE r.from <= NOW() 
AND r.to >=DATE_ADD(NOW(),INTERVAL 1 DAY);

so its cover also this restaurants that close in the next day

Answer 3

This isn't the most efficient way to do it, but it does give the results you're looking for...

I'm guessing your to and from are TIME columns. Since comparison of TIMEs can't figure for a date difference, you'll want to change them into dates before comparing them:

SELECT *,
DATE_ADD(CURDATE(), INTERVAL DATE_FORMAT(r.from, '%H:%i') HOUR_MINUTE) AS from_date,
IF (r.to > r.from,
    DATE_ADD(CURDATE(), INTERVAL DATE_FORMAT(r.to, '%H:%i') HOUR_MINUTE),
    DATE_ADD(CURDATE(), INTERVAL DATE_FORMAT(r.to, '+1 %H:%i') DAY_MINUTE)
) AS to_date
FROM restaurants r
HAVING NOW() BETWEEN from_date AND to_date;

The reason for the inefficiency is that MySQL has to go through the whole table to figure out all the from_date and to_dates and THEN check the HAVING comparison (HAVING allows for conditions on computed columns).