phpmysqldatetimecodeigniteractiverecord

Codeigniter Date comparison - Greater than or Less than datetime field not working

I have the following queries in codeigniter to find any data in which the datetime field is more than 24 hours. Here is my try...

$this->db->where('date_reg >', 'DATE_SUB(NOW(), INTERVAL 1 DAY)');

But the above query is not working!

What I want in short is, return all rows which the date_reg is more than 24 hours...

I can not use query in codeigniter! Here is the format for date_reg field....2019-02-19 08:00:00

How do i do that?

Solution

  • There are two fundamental problems with your coding attempt.

    1. Using < will qualify rows with datetime values which are "older" than the calculated datetime for yesterday and
    2. You must explicitly tell CodeIgniter to not quote the second parameter of your where() method call by passing false as the third parameter.

    If you don't prevent quoting, DATE_SUB(NOW(), INTERVAL 1 DAY) will get wrapped in back ticks or double quotes (depending on your settings) -- and the database, of course, will not find a column called DATE_SUB(NOW(), INTERVAL 1 DAY).

    To find rows with datetimes older than 24 hours ago (date_reg is more than 24 hours):

    $this->db->where('date_reg <', 'DATE_SUB(NOW(), INTERVAL 1 DAY)', false);

    To find rows with datetimes newer than 24 hours ago:

    $this->db->where('date_reg >', 'DATE_SUB(NOW(), INTERVAL 1 DAY)', false);