Find next time business is open; mysql hours calculation

Question

I’m trying to figure out if a shop is currently within its opening hours, if not then select the next time its open.

Finally I need to be able to put the opening day as a specific date.

Can someone possible give me a tip how to construct this query?

Thanks in advance

CREATE TABLE `shop_hours` (
  `id` int(11) NOT NULL,
  `shop_id` int(11) unsigned NOT NULL,
  `day_of_week` int(11) unsigned NOT NULL,
  `open_time` time NOT NULL,
  `close_time` time NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `shop_hours` (`id`, `shop_id`, `day_of_week`, `open_time`, `close_time`)
VALUES
    (1, 1, 0, '08:00:00', '24:00:00'),
    (2, 1, 1, '08:00:00', '24:00:00'),
    (3, 1, 2, '08:00:00', '24:00:00'),
    (4, 1, 3, '08:00:00', '24:00:00'),
    (5, 1, 4, '08:00:00', '24:00:00'),
    (6, 1, 5, '08:00:00', '24:00:00'),
    (7, 1, 6, '08:00:00', '24:00:00');

Edit:

To clarify a little I'm not looking to find open shops, but only open hours for ONE specific shop. Based on the opening/closing hour, and what time it is now. I will generate some selectable timestamps incremented by 15 minutes.

E.g. if a shop has just closed (1PM), I will need to use the next open day's open/closing time instead. (the shop isn't necessarily open every day, could be closed Sundays).

Answer 1

To find out shop_id's, that is open for NOW()

SELECT *
  FROM `shop_hours`
 WHERE `day_of_week` = DATE_FORMAT(NOW(), '%w')
   AND CURTIME() BETWEEN `open_time` AND `close_time`

Obsolete:

To find tomorrow's available open_times:

SELECT *
  FROM `shop_hours`
 WHERE `day_of_week` = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 DAY), '%w')

Edit 2:

To find next available open_times:

  SELECT `shop_id`,
         MIN(CAST(CONCAT(DATE(DATE_ADD(NOW(), INTERVAL ((7 + DATE_FORMAT(NOW(), '%w') - `day_of_week`) % 7) DAY)), ' ', `open_time`) AS DATETIME)) AS `next_open_datetime`
    FROM `shop_hours`
GROUP BY `shop_id`

Edit:

DATE_FORMAT(*DATE*, '%w') uses the format 0 = Sunday ... 6 = Saturday

If you want to use the ISO format 1 = Monday ... 7 = Sunday in your day_of_week field, you should bind php's date('N') to your query (or use Mysql's if function IF(DATE_FORMAT(NOW(), '%w') = 0, 7, DATE_FORMAT(NOW(), '%w')), but that's ugly)

Answer 2

1) Check if shop is open. Result is empty, if shop is closed:

select * from shop_hours
where shop_id = $id
      and dayofweek(curdate()) = day_of_week
      and curtime() between open_time and close_time;

2) Find next open time:

(select open_time from shop_hours
 where shop_id = $id and curtime() < open_time
       and day_of_week >= dayofweek(curdate()))
union
(select open_time from shop_hours
 where shop_id = $id and curtime() < open_time
 order by day_of_week)
 union
(select open_time from shop_hours
 where shop_id = $id and curtime() > close_time
       and day_of_week >= dayofweek(curdate()))
union
(select open_time from shop_hours
 where shop_id = $id and curtime() > close_time
 order by day_of_week)
limit 1;

Untested, but this should respect weekend wraparound and holes in the week (i.e. closed days).

Keep in mind, that dayofweek() numbers 1 = Sunday, 2 = Monday, ... If your table stores the weekdays in a different format, you must adjust the query accordingly.