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

I'm honestly not sure what a cursor is used for - basically I just need to be fetch the next opening- and closing hours, also for a specific date. — Kristian
– Kristian, Commented Oct 27, 2012 at 13:57
how are records looked like? can you post also the results you desired? — Ambrose
– Ambrose, Commented Oct 27, 2012 at 13:57
@Kristian its use is that its the primary key. That's quite a real use. — Burhan Khalid
– Burhan Khalid, Commented Oct 27, 2012 at 14:33

pozs · Accepted Answer · 2012-10-27 14:37:14Z

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)

So to find the next open hours, I would first start out by using example 1, if results is empty, I would create a loop incrementing the interval day. Is this effective?
Keep in mind this won't work if you check at say ... 1AM in the morning and the shop is open on that day at 6AM; in other words it would only return those shops that are open when you run the script.
@BurhanKhalid i figured out too, edited to give always the next time it opens.
@pozs your edit 2 example seems very useful! However if I add the close_time in the select, it doesnt match the one of the open_date that is returned? Lastly is it possibly to split the date and open_time? Thank you so much for your help :)
That select basically selects the minimum of all (next 7 days) opening datetimes - you should split to separate date & time in php. And, if you need the matching close_time, you should query against the returned datetime's day-of-week; you can't get them in one query.

Olaf Dietsche · Accepted Answer · 2012-10-27 15:33:13Z

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.

Collectives™ on Stack Overflow

Find next time business is open; mysql hours calculation

2 Answers 2

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Linked

Related