MySQL Query for calculating the days between 2dates group by months

I have 2days like check_in(25/04/2011), and a check_out(04/06/2011). Now I need to calculate the days be开发者_StackOverflow中文版tween the dates in month wise.

ie. Month - Days,

April  -   5, 
May    -   31, 
June   -   4, 

Please help me in building as mysql query for getting the above result. Thanks in advance.

If you want strictly done in MySQL, you need to create a Stored Procedure to do this.

Something like this in line of Stored Procedure (and range is not higher than a year).

DROP PROCEDURE IF EXISTS `getDateDiffBreakdown`;
CREATE PROCEDURE `getDateDiffBreakdown`(_DATE1 DATE,_DATE2 DATE)
BEGIN

IF (MONTH(_DATE1)<>MONTH(_DATE2)) THEN
-- we detected a month change
-- compute the selection based on current date and last day of month
SELECT CONCAT(DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(LAST_DAY(_DATE1))-TO_DAYS(_DATE1)+1);
-- step into next month and re-run the calc
call getDateDiffBreakdown(DATE_ADD(LAST_DAY(_DATE1),INTERVAL 1 DAY),_DATE2);

ELSE
-- same month, do the calculation
SELECT CONCAT(DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(_DATE2)-TO_DAYS(_DATE1)+1);
END IF;

END;

call like this:

set max_sp_recursion_depth = 11;
call getDateDiffBreakdown('2011-12-11','2012-06-03');

UPDATE

In another approach to get in 1 line, it would be:

DROP PROCEDURE IF EXISTS `getDateDiffBreakdown2`;
CREATE PROCEDURE `getDateDiffBreakdown2`(IN _DATE1 DATE,IN _DATE2 DATE, INOUT _RETURN VARCHAR(1000))
BEGIN

IF (MONTH(_DATE1)<>MONTH(_DATE2)) THEN
-- we detected a month change
-- compute the selection based on current date and last day of month
SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(LAST_DAY(_DATE1))-TO_DAYS(_DATE1)+1);
SET _RETURN = CONCAT(_RETURN,",");
-- step into next month and re-run the calc
call getDateDiffBreakdown2(DATE_ADD(LAST_DAY(_DATE1),INTERVAL 1 DAY),_DATE2,_RETURN);

ELSE
-- same month, do the calculation
SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(_DATE2)-TO_DAYS(_DATE1)+1);
END IF;

END;

call like this:

set max_sp_recursion_depth = 255;
set @TEMP = '';
call getDateDiffBreakdown2('2011-12-11','2012-06-03',@TEMP);
SELECT @TEMP;

I was trying to solve the problem too. Pentium10 is too strong and now I'll try his solution. :) By the way this is mine.

delimiter //
drop procedure if exists groupDaysByMonth//
create procedure groupDaysByMonth(in dStart date,in dEnd date)
begin
declare i int default 0;
declare months,days int;
drop table if exists t;
create temporary table t (
month_year varchar(50),
daysNum int
);
set months = (select period_diff(date_format(dEnd,'%Y%m'),date_format(dStart,'%Y%m')));
while i<=months do
if months = 0 then
set days = (select datediff(dEnd,dStart));
elseif i = 0 then
set days = ( select datediff(concat(date_format(dStart,'%Y-%m-'),day(last_day(dStart))),dStart));
elseif months = i then
set days = (select datediff(dEnd,date_format(dEnd,'%Y-%m-01'))+1);
else
set days = ( select day(last_day(dStart + interval i month)));
end if;
insert into t (month_year,daysNum) values(date_format(dStart + interval i month,'%M %Y'),days);
set i = i + 1;
end while;
select * from t;
end //
delimiter ;


mysql> call groupDaysByMonth('2011-04-25','2011-04-30');
+------------+---------+
| month_year | daysNum |
+------------+---------+
| April 2011 |       5 |
+------------+---------+
1 row in set (0.01 sec)

mysql> call groupDaysByMonth('2011-04-25','2011-06-04');
+------------+---------+
| month_year | daysNum |
+------------+---------+
| April 2011 |       5 |
| May 2011   |      31 |
| June 2011  |       4 |
+------------+---------+
3 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)


mysql> call groupDaysByMonth('2011-09-25','2012-05-02');
+----------------+---------+
| month_year     | daysNum |
+----------------+---------+
| September 2011 |       5 |
| October 2011   |      31 |
| November 2011  |      30 |
| December 2011  |      31 |
| January 2012   |      31 |
| February 2012  |      29 |
| March 2012     |      31 |
| April 2012     |      30 |
| May 2012       |       2 |
+----------------+---------+
9 rows in set (0.01 sec)

Query OK, 0 rows affected (0.03 sec)

Hope that it helps.

    Follow Answer 1 -- i am using it for multi year n the result set is month number with year and then the days of month   format is "month number - last two digit of year - total days in month."

you can change the format of display according your need


**UPDATE**

In another approach to get in 1 line, it would be:

    DROP PROCEDURE IF EXISTS `getDateDiffBreakdown2`;
    CREATE PROCEDURE `getDateDiffBreakdown2`(IN _DATE1 DATE,IN _DATE2 DATE, INOUT _RETURN VARCHAR(1000))
    BEGIN

    IF (MONTH(_DATE1)<>MONTH(_DATE2)) THEN
    -- we detected a month change
    -- compute the selection based on current date and last day of month
    SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(LAST_DAY(_DATE1))-TO_DAYS(_DATE1)+1);
    SET _RETURN = CONCAT(_RETURN,",");
    -- step into next month and re-run the calc
    call getDateDiffBreakdown2(DATE_ADD(LAST_DAY(_DATE1),INTERVAL 1 DAY),_DATE2,_RETURN);

    ELSE
    -- same month, do the calculation
    SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(_DATE2)-TO_DAYS(_DATE1)+1);
    END IF;

    END;

call like this:

    set max_sp_recursion_depth = 255;
    set @TEMP = '';
    call getDateDiffBreakdown2('2011-12-11','2012-06-03',@TEMP);
    SELECT @TEMP;









I have applied this post in one of my requirement but i found it buggy. i can be wrong if any one prove it and provide a better solution.

look how i am calling it and what i am getting :

   set max_sp_recursion_depth = 255;
    set @TEMP = '';
    call getDateDiffBreakdown2('2010-12-10' , '2011-12-10',@TEMP);
    SELECT @TEMP;


in result i get :  '12 - 10 - 366'

year is changed but month is same.

i have tweaked the function as following: kindly let me know if some thing is strange. thanks




 DROP PROCEDURE IF EXISTS `getDateDiffBreakdown2`;
    CREATE PROCEDURE `getDateDiffBreakdown2`(IN _DATE1 DATE,IN _DATE2 DATE, INOUT _RETURN VARCHAR(1000))
    BEGIN

    IF(YEAR(_DATE1)<>YEAR(_DATE2)) THEN

SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%b - %y'),' - ',TO_DAYS(LAST_DAY(_DATE1))-TO_DAYS(_DATE1)+1);
SET _RETURN = CONCAT(_RETURN,",");
-- step into next month and re-run the calc
call getDateDiffBreakdown2(DATE_ADD(LAST_DAY(_DATE1),INTERVAL 1 DAY),_DATE2,_RETURN);

ELSEIF (MONTH(_DATE1)<>MONTH(_DATE2)) THEN
    -- we detected a month change
    -- compute the selection based on current date and last day of month
    SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(LAST_DAY(_DATE1))-TO_DAYS(_DATE1)+1);
    SET _RETURN = CONCAT(_RETURN,",");
    -- step into next month and re-run the calc
    call getDateDiffBreakdown2(DATE_ADD(LAST_DAY(_DATE1),INTERVAL 1 DAY),_DATE2,_RETURN);

    ELSE
    -- same month, do the calculation
    SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(_DATE2)-TO_DAYS(_DATE1)+1);
    END IF;

    END;

Use TO_DAYS(date) function for that.