在MySQL中如何判断当前时间是否落在指定的营业时间区间内

不同数据库对JSON的处理语法存在差异，以下是主流数据库的实现方式：

MySQL 8.0+ 实现 #

通过JSON_TABLE函数将JSON数组的每个时间区间拆分为独立行，再拆分区间的起止时间和当前时间匹配即可：

SELECT DISTINCT d.*
FROM data d
JOIN JSON_TABLE(
    d.open_hours,
    '$[*]' COLUMNS (time_range VARCHAR(20) PATH '$')
) AS ranges
WHERE 
    TIME(NOW()) BETWEEN 
    SUBSTRING_INDEX(ranges.time_range, '-', 1) 
    AND 
    SUBSTRING_INDEX(ranges.time_range, '-', -1);

如果使用的是不支持JSON_TABLE的低版本MySQL，可以用辅助索引表的方式拆解数组：

SELECT *
FROM data
WHERE EXISTS (
    SELECT 1
    -- 这里的索引数量需要覆盖你业务中最多的单日营业时间区间数
    FROM (SELECT 0 AS idx UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS indexes
    WHERE 
        JSON_EXTRACT(open_hours, CONCAT('$[', indexes.idx, ']')) IS NOT NULL
        AND TIME(NOW()) BETWEEN 
            SUBSTRING_INDEX(JSON_UNQUOTE(JSON_EXTRACT(open_hours, CONCAT('$[', indexes.idx, ']'))), '-', 1)
            AND 
            SUBSTRING_INDEX(JSON_UNQUOTE(JSON_EXTRACT(open_hours, CONCAT('$[', indexes.idx, ']'))), '-', -1)
);

PostgreSQL 实现 #

通过jsonb_array_elements_text展开JSON数组即可：

SELECT DISTINCT d.*
FROM data d,
     jsonb_array_elements_text(d.open_hours::jsonb) AS ranges(time_range)
WHERE 
    CURRENT_TIME BETWEEN 
    split_part(ranges.time_range, '-', 1)::time
    AND 
    split_part(ranges.time_range, '-', 2)::time;

如果你的open_hours字段是json类型而非jsonb，把jsonb_array_elements_text改为json_array_elements_text即可。

通用注意事项 #

• 如果存在跨零点的营业时间（比如"22:00-02:00"），上述BETWEEN逻辑会失效，需要额外判断：如果区间结束时间小于开始时间，改为匹配「当前时间≥开始时间 OR 当前时间≤结束时间」即可。
• 语句中加DISTINCT是为了避免单条记录匹配到多个营业时间区间时返回重复结果。

内容的提问来源于stack exchange，提问作者user16256930