MySQL time between days

Question

I have an application that saves the opening time of a restaurant:

My code is to open open restaurants. My SQL query (simplified) looks like

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

The problem here is that there is a record that rolls over - this is for a restaurant open from 11:00 to 3:00 the next day.

What would be a good request to capture this 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 this to SQL:

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

You can also check places that are open 24 hours. The following code assumes that you have set tags fromand tothe same (for example, 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

, DB datetime ( NOW())

:

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

, .

Answer 3

, , ...

, to from TIME. TIME , :

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;

, MySQL , from_date to_dates, HAVING (HAVING ).