phpcodeignitertimewhere-clausedatetime-comparison

SELECT database records WHERE time values stored in "h:i a" format are within a qualifying range using CodeIgniter's query builder


Here is a query that I am using to compare time in my CodeIgniter model:

$present_time = date("h:i a");
$this->db->where('status!=', 'closed ');
$this->db->where('one_hour_break!=', ' ');
$this->db->where("(one_hour_break ='$present_time' or one_hour_break < '$present_time')");
$query = $this->db->get('student');
$final_time = $query->result();

if($final_time)
    {
        echo "true";
    }
else
    {
        echo "false";
    }

It is comparing the time properly if it is between 1 and 12 however it is not comparing time properly between the given time is between 12pm to 1pm and 12am to 1pm

e.g :

So if present time: 12:30 pm and one_hour_break: 1:30 pm, then one hour break should be greater than present time, and the result should say false, however it is saying true. The same goes with the time between 12pm to 1pm.

Can anyone please tell how to manage this issue?


Solution

  • There are a few ways to fix this, the best way is to go back and store the times in the database with "Y-m-d H:i" or something like it.
    That way you can get a fixed timestamp of when, and not only time of day which is useless most of the times when you develop.

    Then you use strtotime() http://php.net/manual/en/function.strtotime.php to convert the date in string format to a numeric value that can easily be compared.