我的表中有這些行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')
任何解決方案。謝謝
uj5u.com熱心網友回復：
試試下面的查詢，我希望你能得到你的結果。
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')
uj5u.com熱心網友回復：
嘗試使用以下代碼：
SELECT *
FROM booking
WHERE start BETWEEN '2022-10-27 12:20:00'
AND '2022-10-27 14:50:00';
uj5u.com熱心網友回復：
試試下面的代碼：
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'])));
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/528307.html