获取代码点火器中存在于日期时间范围之间的行

Question

我的表中有这些行，数据库中有start和end列，数据类型为datetime

start                 |    end 
1    2022-10-27 11:59:00       2022-10-27 01:00:00
2    2022-10-28 01:59:00       2022-10-28 05:00:00
3    2022-11-22 11:59:00       2022-11-22 07:00:00
4    2022-11-25 01:59:00       2022-11-25 06:00:00

使用该查询检索位于给定日期-时间组合之间的行数

$this->db->query("SELECT * FROM booking WHERE (TIMEDIFF('$start_time', TIME(start)) >=0 AND TIMEDIFF('$start_time', TIME(end)) <= 0) AND (TIMEDIFF('$end_time', TIME(start)) >=0 AND TIMEDIFF('$end_time', TIME(end)) <= 0) AND user_id=$user_id")->num_rows();

其中start_time和end_time的值类似

$start_time=13:00:00
$end_time=22:59:00
$start_time=date('Y-m-d H:i:s', strtotime($fromDate.$start_time));
$end_time=date('Y-m-d H:i:s', strtotime($toDate.$end_time));

也尝试了此查询，但不起作用

select * from booking where (start between '2022-10-27 12:20:00' and '2022-10-27 14:50:00') AND (end between '2022-10-27 12:20:00' and '2022-10-27 14:50:00')

任何解决方案。感谢

Answer 1

尝试下面的查询，我希望您能得到结果。

select * from booking where start <= '2022-10-27 12:20:00' and  start >= '2022-10-27 14:50:00'AND end between '2022-10-27 12:20:00' and '2022-10-27 14:50:00')

Answer 2

尝试使用以下代码：

SELECT *
FROM booking
WHERE start BETWEEN '2022-10-27 12:20:00'
AND '2022-10-27 14:50:00';

Answer 3

尝试以下代码：

public function dates()
{
$daterange = $this->input->get('daterange');
$start_date = date('m/01/Y');
$end_date =  date('m/31/Y');
if ($daterange) {
$daterange = explode('-',  $daterange);
$start_date =  $daterange[0];
$end_date =  $daterange[1];
}
$where['start_date'] = $start_date;
$where['end_date'] = $end_date;
$this->model_name->method($where);
}
Model:
if ($where['start_date']) {
$this->db->where('date >=', date('Y-m-d', strtotime($where['start_date'])));***emphasized text***
} 
if ($where['end_date']) {enter code here
$this->db->where('date <=',  date('Y-m-d', strtotime($where['end_date'])));
}

Answer 4

您可以尝试下面的查询，我希望您能得到结果。

$start_date = '2022-10-27';
$end_date = '2022-10-28';
//where table_name 'booking'
SELECT *
FROM booking
WHERE start >= '$start_date' 
AND end <= '$end_date';