Fetch row which exist between date time range in codeigniter

Question

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

Just a note but BETWEEN will never work here as BETWEEN requires the lower number (ordinal etc) to be on the left. Yours flips the start / end times around. — Forbs, Oct 27 '22 at 19:13

score 0 · Answer 1 · answered Oct 27 '22 at 05:06

0

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

answered Oct 27 '22 at 05:06

Kamiyab Sunasara

1

score 0 · Answer 2 · edited Oct 31 '22 at 03:19

0

Try using the below code:

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

edited Oct 31 '22 at 03:19

James Risner

5,451
11
25
47

answered Oct 27 '22 at 08:47

skjalal

19
3

Nilesh Karanjkar · Answer 3 · 2022-11-05T18:35:53.627

Try below code:

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'])));
            }

score 0 · Answer 4 · answered Dec 01 '22 at 04:16

0

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

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

answered Dec 01 '22 at 04:16

Rashedul Hasan

16
1

Fetch row which exist between date time range in codeigniter

4 Answers4