How to save working hours of multiple stores and then query it without duplication?

Question

I'm want to create database with stores. Each store has their own working hours in a week. Then I want to query the store and sort them by nearest store which is currently open. So far I create this schema but unfortunatly I cannot query it like I would like to. The schema looks like this

This is the query I'm using currently (for day: Saturday and hour: 3:15 PM):

SELECT so.day, so.store_id, so.open, so.close 
FROM shop_openings so 
ORDER BY FIELD(so.day, 6, 7, 1, 2, 3, 4, 5) ASC, 
         '15:15' COLLATE utf8mb4_unicode_520_ci 
                 NOT BETWEEN CONCAT( LPAD( so.open, 2, '0'), ':00') 
                         AND CONCAT( LPAD( so.close, 2, '0'), ':00'), 
         LPAD( so.close, 2, '0' ), 
         LPAD( so.open, 2, '0' )

But it query just like the screen above shows.

Now here's what the list I want to achieve:

Let we say that today is Saturday. We have 4 stores which only 2 of them works in Saturday. I want to recieve 4 results order by day and open/close hours. Below I selected which rows I want to get:

No other results. Only 4 results as we have 4 stores in store_table. Nearest 2 shops opened in Saturday and then 2 shops opened in Monday.

I have no idea how the schema should like other than mine, so any suggestion are welcome.

Here is a SQL Fiddle: http://sqlfiddle.com/#!9/9709e/1/0

Answer 1

If you only want the next day, and sundays are closed you can use this query,, when you want the next two days You have to UNION another Query and exclude all previous shops like i did in the WHERE clause

You can replace the @searchday session variable with DAYOFWEEK(NOW()) to get the actual day

CREATE TABLE `shop_openings` (
  `shop_id` int,
  `day` int,
  `open` varchar(5),
  `close` varchar(5)
  );

  INSERT INTO `shop_openings` VALUES
  (10, 6, '9:00', '16:00'),
  (10, 5, '9:00', '16:00'),
  (10, 4, '9:00', '16:00'),
  (10, 3, '9:00', '16:00'),
  (10, 2, '9:00', '16:00'),
  (10, 1, '9:00', '16:00'),
  (11, 6, '10:00', '18:00'),
  (11, 4, '10:00', '18:00'),
  (11, 2, '9:00', '18:00'),
  (12, 5, '9:00', '17:00'),
  (12, 3, '9:00', '17:00'),
  (12, 1, '9:00', '17:00'),
  (13, 4, '9:00', '16:00'),
  (13, 3, '9:00', '16:00');

✓

✓

SET @searchday = 6;
SELECT so.day, so.shop_id, so.open, so.close 
FROM shop_openings so 
WHERE `day` = @searchday
UNIOn
SELECT so.day, so.shop_id, so.open, so.close 
FROM shop_openings so 
WHERE `day` = IF(@searchday = 7 Or @searchday = 6,1,@searchday + 1)
AND shop_id NOT IN(SELECT so.shop_id 
FROM shop_openings so 
WHERE `day` = @searchday)

✓

day | shop_id | open  | close
--: | ------: | :---- | :----
  6 |      10 | 9:00  | 16:00
  6 |      11 | 10:00 | 18:00
  1 |      12 | 9:00  | 17:00

db<>fiddle here

Answer 2

Given input of @day=6 for Saturday, replace

ORDER BY FIELD(so.day, 6, 7, 1, 2, 3, 4, 5) ASC

by

ORDER BY 
    IF(`day` < @day, `day` + 7, `day`),   -- wrap the days (Mon = 8, etc)

and tack this on the end:

LIMIT 4

You might want to LPAD the incoming time.