0

I want to get records which exists between given start time and end time, start time and end time will be given by user, i'm using the following query for that :

$this->db->where('TIME(start) BETWEEN "' . date('H:i:s', strtotime($start_time)) . '" and "' . date('H:i:s', strtotime($end_time)) . '"');
                    $this->db->where('TIME(end) BETWEEN "' . date('H:i:s', strtotime($start_time)) . '" and "' . date('H:i:s', strtotime($end_time)) . '"');
$results = $this->db->get('class')->result();

start and end time column is datetime field:

eg. in database start time : 2020-06-08 06:15:00 end time : 2020-06-08 09:15:00

user3653474
  • 3,393
  • 6
  • 49
  • 135
  • shouldn't it be `date('Y-m-d H:i:s', strtotime($start_time))`? – sauhardnc Jun 17 '20 at 08:49
  • No i want to compare only time, and i think issue is not in query : can i write something like this : date('H:i:s'), strtotime($start_time) between TIME('start') AND TIME('end') AND date('H:i:s'), strtotime($end_time) between TIME('start') AND TIME('end') – user3653474 Jun 17 '20 at 08:58
  • because i want to check only whether my start time and end time lies between database start time and end time – user3653474 Jun 17 '20 at 08:58
  • try use `$this->db->get_compiled_select();` too see your compiled query – kirb Jun 17 '20 at 09:09

1 Answers1

0

@user3653474 You can use TIMEDIFF to check if the time provided is between the values of two columns or not. See if the query below does your deed.

$start = date('H:i:s', strtotime($start_time)); // get time from date in desired format
$end   = date('H:i:s', strtotime($end_time));

// where $start is between column {start} and {end} AND $end is between column {start} and {end}
$sql   = "SELECT * FROM class 
          WHERE (TIMEDIFF('$start', TIME(start)) >=0 AND TIMEDIFF('$start', TIME(end)) <= 0) 
          AND (TIMEDIFF('$end', TIME(start)) >=0 AND TIMEDIFF('$end', TIME(end)) <= 0)"; 

$results = $this->db->query($sql)->result();
sauhardnc
  • 1,961
  • 2
  • 6
  • 16