-1

I have a table that looks like this.

id open_hours other
1 ["09:00-14:30", "19:00-21:30"] jj
2 ["10:00-14:00"] kk
3 ["01:00-04:00", "05:00-08:00", "10:00-15:00", "16:00-00:00"] pp

and I want to know if the at the moment(NOW()) the store is open, i.e. it falls in one of the range given for a particular day. How can I do that within an SQL query? I am trying to achieve it by something like

SELECT * FROM data
WHERE TIME(NOW) BETWEEN one of the ranges specified in open_hours;
Akina
  • 39,301
  • 5
  • 14
  • 25
user16256930
  • 3
  • 2
  • 2
    save data in a normalized database and also read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – nbk Dec 05 '21 at 23:45
  • Is this table structure designed by you? Or, is the a task or a test given to you and you have no access to change the table structure etc. and only have access to run `SELECT` query? – FanoFN Dec 06 '21 at 01:04
  • @FanoFN This structure is designed by me. – user16256930 Dec 06 '21 at 03:42
  • So, I assume that in `| id=1 | "09:00-14:30"` , the first value(`09:00`) is "open time" and the second value(`14:30`) is "closing time" for that range, is it? What `"19:00-21:30"` represents then? Also are these `open_hours` by `id` values are fixed or are these values being stored in; for example like need to press a button on a time-record machine - that will execute a command to store the "pressed time" into the database? Also you mention _"for a particular day"_ but the table don't have a date record. – FanoFN Dec 06 '21 at 06:16
  • What is **precise** MySQL version? *I have a table that looks like this.* Provide it as CREATE TABLE +INSERT INTO scripts. – Akina Dec 06 '21 at 06:19
  • Just because MySQL supports JSON does not mean you HAVE to use it. Normalize the data. Store ["01:00-04:00", "05:00-08:00", "10:00-15:00", "16:00-00:00"] as 4 rows, each having two columns with datatype = time. – Salman A Dec 06 '21 at 08:57

1 Answers1

0
SELECT test.id, 
       CAST(SUBSTRING_INDEX(jsontable.timerange, '-', 1) AS TIME) timestart, 
       CAST(SUBSTRING_INDEX(jsontable.timerange, '-', -1) AS TIME) timeend,
       test.other
FROM test
CROSS JOIN JSON_TABLE(test.open_hours,
                      '$[*]' COLUMNS (timerange VARCHAR(255) PATH '$')) jsontable
HAVING CURRENT_TIME BETWEEN timestart AND timeend;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=30b7dfac8d89b774a7ddb29f890889d9

PS. I strongly advise you to follow the advice of nbk and normalize the data.

Akina
  • 39,301
  • 5
  • 14
  • 25