Question 112994 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Fetch row which exist between date time range in codeigniter

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

Tags:

4

Answers


  1. 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')
    Login or Signup to reply.
  2. 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';
    Login or Signup to reply.
  3. 0

    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'])));
            }
    Login or Signup to reply.
  4. 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';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search