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

Question

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?

Possible duplicate of [Compare two dates in Codeigniter and MySQL](https://stackoverflow.com/questions/6288261/compare-two-dates-in-codeigniter-and-mysql) — Sinto, Feb 21 '19 at 11:22
it is not duplicate, my field is datetime type. that is big different — MR.Internet, Feb 21 '19 at 11:33
Hope you can do that as `echo date('Y-m-d H:i:s', strtotime('-1 day', strtotime(date('Y-m-d H:i:s'))));` ? — Sinto, Feb 21 '19 at 12:20

score 3 · Answer 1 · answered Feb 21 '19 at 12:26

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

Abdulrehman Sheikh · Answer 2 · 2019-02-21T12:22:35.583

1

Try this:

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

edited Feb 21 '19 at 12:22

answered Feb 21 '19 at 11:28

Abdulrehman Sheikh

931
9
15

not working, the issue might be my date_reg field type is datetime, is that might be the problem? – MR.Internet Feb 21 '19 at 11:38
I have tested it. SELECT * FROM `customer_entity` WHERE created_at >= (NOW() + INTERVAL 1 DAY) Its working on my side for my customer column. – Abdulrehman Sheikh Feb 21 '19 at 11:38
I also have datetime thats not a problem you can do one more thing let me share – Abdulrehman Sheikh Feb 21 '19 at 11:39
$this->db->where('DATE(date_reg)', '>=', '(NOW() + INTERVAL 1 DAY)'); – Abdulrehman Sheikh Feb 21 '19 at 11:40
apply DATE() function on your field which will compare date .. try it if it works then will check for hour as well – Abdulrehman Sheikh Feb 21 '19 at 11:40
1

this isn't working at all ... the first parameter is the key the second the value and the third is a bool whether you want to escape it or not (default is `true`) - take a look here https://www.codeigniter.com/user_guide/database/query_builder.html?highlight=where#CI_DB_query_builder::where a proper solution would be `$this->db->where('date_reg >=', '(NOW() + INTERVAL 1 DAY)', false);` – Atural Feb 21 '19 at 12:04

score 0 · Answer 3 · answered Feb 21 '19 at 11:21

0

Try this;

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

answered Feb 21 '19 at 11:21

Gündoğdu Yakıcı

677
2
10
31

I can not use query, because I have a lot of other info. It should be as codeigniter style... – MR.Internet Feb 21 '19 at 11:24
Okay, try. $this->db->where('date_reg >=', 'NOW() + INTERVAL 1 DAY'); – Gündoğdu Yakıcı Feb 21 '19 at 11:25
$this->db->where('date_reg >=', DATE_SUB(NOW(), INTERVAL 24 HOUR)'); – Gündoğdu Yakıcı Feb 21 '19 at 11:29
Can you try the last one? – Gündoğdu Yakıcı Feb 21 '19 at 11:30
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/188809/discussion-between-dodis-and-mr-internet). – Gündoğdu Yakıcı Feb 21 '19 at 11:35

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

3 Answers3