Fetch row which exist between date time range in codeigniter-CodePudding

I have these rows in my table with start and end column in database like these are of datatype 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

using this query to retrieve the number of rows which lies between the given date time combinations

$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();

where start_time and end_time values are like

    $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));

tried this query also but not working

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')

Any solution. Thanks

CodePudding user response：

try below query, I hope you will get your result.

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')

CodePudding user response：

Try using the below code:

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