Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL - DATE_ADD month interval

I face a problem with the function DATE_ADD in MySQL.

My request looks like this :

SELECT *  FROM mydb  WHERE creationdate BETWEEN "2011-01-01" AND DATE_ADD("2011-01-01", INTERVAL 6 MONTH)  GROUP BY MONTH(creationdate) 

The problem is that, in the results, -I think- because June has only 30 days, the function doesn't work properly as I have the results of the first of July.

Is there a way to tell DATE_ADD to work well and take the right number of days within a month?

like image 712
B F Avatar asked Jul 27 '11 13:07

B F


People also ask

How do I add 12 months to a date in MySQL?

The DATE_ADD() function adds a time/date interval to a date and then returns the date.

Does Dateadd work in MySQL?

DATE_ADD() function in MySQL is used to add a specified time or date interval to a specified date and then return the date. Specified date to be modified. Here the value is the date or time interval to add.

How does MySQL calculate time intervals?

The TIMEDIFF() function returns the difference between two time/datetime expressions. Note: time1 and time2 should be in the same format, and the calculation is time1 - time2.


2 Answers

DATE_ADD works just fine with different months. The problem is that you are adding six months to 2001-01-01 and July 1st is supposed to be there.

This is what you want to do:

SELECT *  FROM mydb  WHERE creationdate BETWEEN "2011-01-01"                     AND DATE_ADD("2011-01-01", INTERVAL 6 MONTH) - INTERVAL 1 DAY GROUP BY MONTH(creationdate) 

OR

SELECT *  FROM mydb  WHERE creationdate >= "2011-01-01"  AND creationdate < DATE_ADD("2011-01-01", INTERVAL 6 MONTH) GROUP BY MONTH(creationdate) 

For further learning, take a look at DATE_ADD documentation.

*edited to correct syntax

like image 73
Adriano Carneiro Avatar answered Sep 19 '22 17:09

Adriano Carneiro


Well, for me this is the expected result; adding six months to Jan. 1st July.

mysql> SELECT DATE_ADD( '2011-01-01', INTERVAL 6 month ); +--------------------------------------------+ | DATE_ADD( '2011-01-01', INTERVAL 6 month ) | +--------------------------------------------+ | 2011-07-01                                 |  +--------------------------------------------+ 
like image 28
wonk0 Avatar answered Sep 19 '22 17:09

wonk0