sql 读取一段日期中每一天某个时间段的所有数据

参考博客：SQL-读取表中每天的某个时间段的数据_熊博主-CSDN博客_sql查询每天固定时间的数据

页面是时间段多选框        用的是layui的样式

 页面html代码

<div class="layui-form-item">
        <label class="layui-form-label labelsamll">违法时段</label>
        <div class="layui-input-inline" style="width: auto">
             <input type="checkbox" name="time" value="00-04" title="0-4">
                 <input type="checkbox" name="time" value="04-06" title="4-6">
                 <input type="checkbox" name="time" value="06-12" title="6-12">
                 <input type="checkbox" name="time" value="12-14" title="12-14">
                 <input type="checkbox" name="time" value="14-18" title="14-18">
                 <input type="checkbox" name="time" value="18-20" title="18-20">
                 <input type="checkbox" name="time" value="20-22" title="20-22">
                 <input type="checkbox" name="time" value="22-24" title="22-24">
                 <input type="hidden" name="timeList" id="timeList">
        </div>
</div>

js代码

//获取复选框的值转为数组
var checkValues = $('input[type=checkbox]:checked').map(function(){
      return $(this).val();
}).get();
 //checkValues是一个由checkbox的value组成的数组，例如【00-04,12-14】
let check = ""
if (checkValues.length === 1) {
     check = checkValues[0]
} else {
      check = delTime(checkValues)
}
//check的格式为00-04,06-14

// 例如两个相邻就合并，不相邻就用逗号分隔
function delTime (checkValues) {
    //对违法时间段做处理，时间段相连就合并，不相连就用‘，’分隔
    let check = "";
    let newVar = "";
    for (let i = 0; i < checkValues.length - 1; i++) {
        if (newVar === "") {
            newVar = checkValues[i];
         }
         let newVar1 = checkValues[i+1];
         let data = newVar.substring(newVar.lastIndexOf("-") + 1, newVar.length);
         let val = newVar1.substring(0, newVar1.indexOf("-"));
         if (data === val) {
            if (check === newVar) {
               check = newVar.substring(0, newVar.lastIndexOf("-")) + "-" + newVar1.substring(newVar1.indexOf("-") + 1, newVar1.length)
            } else {
               if (check !== "") {
                   check += "," + newVar.substring(0, newVar.lastIndexOf("-")) + "-" + newVar1.substring(newVar1.indexOf("-") + 1, newVar1.length)
                } else {
                   check += newVar.substring(0, newVar.lastIndexOf("-")) + "-" + newVar1.substring(newVar1.indexOf("-") + 1, newVar1.length)
                 }

             }
             newVar = check
      } else {
           if (checkValues.length-2 === i) {
                if (check === newVar) {
                    check = newVar + "," + newVar1
                 } else {
                     check += "," + newVar + "," + newVar1
                  }
             } else {
                  if (check !== "") {
                     if (check !== newVar) {
                        check += "," + newVar
                      }
                   } else {
                      check += newVar
                    }

                    newVar = newVar1
                }
            }
     }
    return check
}

然后把这个对象传到后台，用List<String>接收

不知道如何传集合的可以看看我的另一篇博客：springMVC后台接收类型为集合_认证菜鸟的博客-CSDN博客

最后就是sql语句的书写，我后台用的是mybatis

这个是例子是00-06时间段的

SELECT wfsj,to_char(f.wfsj, 'hh24:mi:ss') FROM tableName f WHERE to_char(f.wfsj, 'hh24:mi:ss') BETWEEN concat(substr('00-06', 0, instr('00-06', '-') - 1), ':00:00') AND concat(substr('00-06', instr('00-06', '-') + 1, length('00-06')), ':00:00')

xml中的写法 ,后端用的List<String> timeList来接收时间段，如何要加上某段时间的每一天，直接在where条件判断力加上就可以了

select * from tableName s
<where>
    <if test="timeList.size() != 0">
      and
      <foreach collection="timeList" index="index" item="item" separator="or" open="(" close=")">
           to_char(s.wfsj, 'hh24:mi:ss') between concat(substr(#{item}, 0, instr(#{item}, '-') - 1), ':00:00')
           and concat(substr(#{item}, instr(#{item}, '-') + 1, length(#{item})), ':00:00')
      </foreach>
    </if>
    <if test="beginWfsj != null">
        and s.wfsj >= #{beginWfsj}
    </if>
    <if test="endWfsj != null">
        and #{endWfsj} >= s.wfsj
    </if>
</where>