MySQL: how to add one day to the datetime field in a query

Question

In my table, I have a field named eventdate in datetime format, for example 2010-05-11 00:00:00 .

How to make a request so that it adds one day to the eventdate , for example, if today is 2010-05-11 , I want to show in the where clause in order to return all records from tomorrow date.

Update:

I tried this:

select * from fab_scheduler where custid = 1334666058 and DATE_ADD(eventdate, INTERVAL 1 DAY)

But unfortunately, it returns the same record, even if I add an interval greater than 1.

Result:

2010-05-12 00:00:00

But I only want to select entries with a date tomorrow.

Answer 1

You can use the DATE_ADD() function:

 ... WHERE DATE(DATE_ADD(eventdate, INTERVAL -1 DAY)) = CURRENT_DATE 

It can also be used in a SELECT :

 SELECT DATE_ADD('2010-05-11', INTERVAL 1 DAY) AS Tomorrow; +------------+ | Tomorrow | +------------+ | 2010-05-12 | +------------+ 1 row in set (0.00 sec) 

Answer 2

 SELECT DATE_ADD(eventdate, INTERVAL 1 DAY) 

Mysql doc

Answer 3

Go with this, as I will do it :)

 SELECT * FROM fab_scheduler WHERE custid = '123456' AND CURDATE() = DATE(DATE_ADD(eventdate, INTERVAL 1 DAY)) 

Answer 4

If you can use NOW (), this will be the simplest form:

 SELECT * FROM `fab_scheduler` WHERE eventdate>=(NOW() - INTERVAL 1 DAY)) AND eventdate<NOW() ORDER BY eventdate DESC; 

With MySQL 5.6+, the abowe query should be executed. Depending on the sql server, you may need to use CURRDATE() instead of NOW() - which is an alias for DATE(NOW()) and will only return a date in the datetime type;

Answer 5

You can use MySQL special syntactic sugar:

 SELECT ... date_field + INTERVAL 1 DAY 

It looks much prettier instead of the DATE_ADD function

Answer 6

How about this:

 select * from fab_scheduler where custid = 1334666058 and eventdate = eventdate + INTERVAL 1 DAY 

Answer 7

You can try the following:

 SELECT DATE(DATE_ADD(m_inv_reqdate, INTERVAL + 1 DAY)) FROM tr08_investment 

Answer 8

 $date = strtotime(date("Ymd", strtotime($date)) . " +1 day"); 

Or, easier:

 date("Ymd H:i:s", time()+((60*60)*24));