CODEWITHSUNDEEP
mysqlcodeigniterdatetime
MR.Internet
MR.Internet

Reputation: 645

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?

Upvotes: 0

Views: 7088

Answers (4)

mickmackusa
mickmackusa

Reputation: 47864

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

Upvotes: 1

Kebab Programmer
Kebab Programmer

Reputation: 1219

Are you looking for an older record that was recorded within 24 hours of the current date? Because let's take today's date. 2019-02-21.

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

This query is essentially saying Find records greater than the current date. So it will look for records between 2019-02-21 and 2019-02-22, which might return empty.

Try this instead. I try to analyze the question as much as possible, rather than throw an half-assed answer

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

Upvotes: 3

Abdul Rehman Sheikh
Abdul Rehman Sheikh

Reputation: 939

Try this:

$this->db->select('*');
$this->db->from('your_table');
$this->db->where('date_reg >=', '(NOW() + INTERVAL 1 DAY)', false);
$this->db->get();

Upvotes: 1

G&#252;ndoğdu Yakıcı
G&#252;ndoğdu Yakıcı

Reputation: 677

Try this;

$this->db->query('SELECT * FROM your_table WHERE date_reg >= now() + INTERVAL 1 DAY;');

Upvotes: 0

Related Questions