0

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

enter image description here

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:

enter image description here

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

4
  • Does your MySQL version is really 5.6?
    – Akina
    Feb 25, 2020 at 19:04
  • > 5.6 but this version in on fiddle
    – Kamil
    Feb 25, 2020 at 19:09
  • From query in that fiddle I want only first 4 results The data (even fieldnames!) in a fidle does not match to ones on the screenshots. Edit either your fiddle or your question (especially desired output) please. And specily your REAL server version.
    – Akina
    Feb 25, 2020 at 19:10
  • @Akina I update a fiddle and my MySQL version is: 5.7.29-0ubuntu0.18.04.1 for Linux on x86_64 ((Ubuntu)) (I dont know how to check the version in fiddle, sorry)
    – Kamil
    Feb 26, 2020 at 17:11

2 Answers 2

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

5
  • It's looking good but I want to see all next days until todays (if today is saturday then i want to see Sat-Fri, Sundays also), so I should make 6 UNION queries? Is it good for performance?
    – Kamil
    Feb 26, 2020 at 17:13
  • You can make a stored procedure out of it, if you want all very flexible, where you select a day and also how many days to search.
    – nbk
    Feb 26, 2020 at 17:55
  • Yes, this sounds like correct way I think but I need to implement this query to existing system and I can just extend existing query
    – Kamil
    Feb 27, 2020 at 9:26
  • could you give an example how to create a procedure with this solution as you said in previous comment?
    – Kamil
    Feb 29, 2020 at 13:00
  • You create a temporary table for your resultset. You have a loop over 7 days, You start from the day wanted for example Saturday And take my Select from above but you insert it directly into the temporary table and exclude all ids that are already in the temporary table. After 7 days you have all the shops in the order of days since startday.
    – nbk
    Feb 29, 2020 at 13:08
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.