CodeIgniter中如何查询指定时间范围内的数据库记录？

首先你的核心问题是部分记录的start时间晚于end时间（跨午夜的时间段），之前的查询逻辑没有处理这种跨天场景，导致无法匹配到目标记录。以下是针对性的解决方案：

问题根源梳理 #

你的数据里存在两种时间段：

1. 正常单日时间段：start <= end（比如第2、4条记录）
2. 跨午夜时间段：start > end（比如第1、3条，实际是从end时间开始，到次日的start时间结束）

之前的查询要么忽略了日期维度（用TIME()函数只取时间部分），要么用between的逻辑无法覆盖跨天场景，所以查不到符合条件的记录。

方案1：查询与指定时间段有重叠的记录数量 #

如果你的需求是找出时间段和指定查询范围有交集的记录，用以下逻辑：

原生SQL示例 #

SELECT COUNT(*) AS count 
FROM booking 
WHERE user_id = 1 -- 替换为你的用户ID
AND (
    -- 处理单日时间段：记录时间段与查询范围有交集
    (start <= end AND start <= '2022-10-27 14:50:00' AND end >= '2022-10-27 12:20:00')
    OR
    -- 处理跨天时间段：记录的跨天区间与查询范围有交集
    (start > end AND (end <= '2022-10-27 14:50:00' OR start >= '2022-10-27 12:20:00'))
);

CodeIgniter 写法（防SQL注入，用参数绑定） #

// 定义查询的起止时间
$query_start = '2022-10-27 12:20:00';
$query_end = '2022-10-27 14:50:00';
$user_id = 1; // 替换为实际用户ID

$sql = "SELECT COUNT(*) AS count FROM booking 
        WHERE user_id = ? 
        AND (
            (start <= end AND start <= ? AND end >= ?) 
            OR 
            (start > end AND (end <= ? OR start >= ?))
        )";

// 执行查询并获取结果
$result = $this->db->query($sql, [$user_id, $query_end, $query_start, $query_end, $query_start])->row();
$record_count = $result->count;

方案2：查询时间段完全落在指定范围内的记录 #

如果你的需求是找出整个时间段都包含在查询范围里的记录，逻辑如下：

原生SQL示例 #

SELECT COUNT(*) AS count 
FROM booking 
WHERE user_id = 1
AND (
    -- 单日时间段：完全在查询范围内
    (start <= end AND start >= '2022-10-27 12:20:00' AND end <= '2022-10-27 14:50:00')
    OR
    -- 跨天时间段：需查询范围覆盖跨天区间（比如查询范围是两天及以上）
    (start > end AND end >= '2022-10-26 00:00:00' AND DATE_ADD(start, INTERVAL 1 DAY) <= '2022-10-28 23:59:59')
);

CodeIgniter 写法 #

$query_start = '2022-10-26 00:00:00';
$query_end = '2022-10-28 23:59:59';
$user_id = 1;

$sql = "SELECT COUNT(*) AS count FROM booking 
        WHERE user_id = ? 
        AND (
            (start <= end AND start >= ? AND end <= ?) 
            OR 
            (start > end AND end >= ? AND DATE_ADD(start, INTERVAL 1 DAY) <= ?)
        )";

$result = $this->db->query($sql, [$user_id, $query_start, $query_end, $query_start, $query_end])->row();
$record_count = $result->count;

优化建议 #

如果可以修改数据存储逻辑，建议把跨午夜的记录的end字段改为次日的日期（比如第1条记录的end改成2022-10-28 01:00:00），这样所有记录的start <= end，查询逻辑会简化很多，直接用：

SELECT COUNT(*) FROM booking 
WHERE user_id = ? 
AND start <= ? 
AND end >= ?;

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