I have a table that looks like this.

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;

CodePudding user response：

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.