How To Get The Difference of a Two Date Value in MySQL and the result is in month wise?

Question

Here I Have The Two Dates Like The Following

2012-08-12 to 2012-10-20

I Need The Output Like the following:

month[08]=19
month[09]=30
month[10]=20

You Can Solve it By PHP,MYSQL

Answer 1

Use PERIOD_DIFF(P1,P2) to get the number of months between two dates.

Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.

mysql> SELECT PERIOD_DIFF(200802,200703);
        -> 11

In your case, you would use:

SELECT PERIOD_DIFF(DATE_FORMAT('2012-10-20', '%Y%m'), DATE_FORMAT('2012-08-12', '%Y%m'))

According to this post, using EXTRACT may be faster:

SELECT
    PERIOD_DIFF(
        EXTRACT(YEAR_MONTH FROM '2012-10-20'), 
        EXTRACT(YEAR_MONTH FROM '2012-08-12')) AS months 
FROM your_table;

Answer 2

If you wanted to build a table in MySQL that contained all the possible date values in the date ranges you are interested in you could do this from a query relatively easily.

The table would simply need to have a single column like

dates
----------
2012-01-01
2012-01-02
...
2050-12-31

Then you could query the table like this:

SELECT YEAR(`dates`) as `years`, MONTH(`dates`) as `months`, COUNT(`dates`) as `days`
FROM date_table
WHERE `dates` BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
GROUP BY `years`, `months`
ORDER BY `years` ASC, `months` ASC

This sort of approach of having a table of dates is commonly used in BI/Data Warehousing applications, but I think it could work here for you.

Note that I added a "years" value in there as well, as I imagine this is something you would need to deal with as well, unless you didn't ever plan to have date ranges that could span across years.