Find next time business is open; mysql hours calculation

mysql, php, select, sql

Solution

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_time`s:

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

Edit 2:

To find next available `open_time`s:

  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)

Problem

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).

Original source