Fetch range from days

New edit:

As I told you in a comment, I strongly recommend you to use the quick query and then process the missing dates in PHP as that would be faster and more readable:

select  concat(@category := category, ',', min(day)) col1,  concat(max(day), ',', @a := a) col2from t, (select @category := '', @a := '', @counter := 0) initwhere @counter := @counter + (category != @category or a != @a)group by @counter, category, a

However, if you still want to use the query version, then try this:

select  @counter := @counter + (category != @category or a != @a) counter,  concat(@category := category, ',', min(day)) col1,  concat(max(day), ',', @a := a) col2from (  select distinct s.day, s.category, coalesce(t1.a, 0) a  from (    select (select min(day) from t) + interval val - 1 day day, c.category    from seq s, (select distinct category from t) c    having day <= (select max(day) from t)  ) s  left join t t1 on s.day = t1.day and s.category = t1.category  where s.day between (    select min(day) from t t2    where s.category = t2.category) and (    select max(day) from t t2    where s.category = t2.category)  order by s.category, s.day) t, (select @category := '', @a := '', @counter := 0) initgroup by counter, category, aorder by category, min(day)

Note that MySQL won't allow you to create data on the fly, unless you hardcode UNIONS, for example. This is an expensive process that's why I strongly suggest you to create a table with only an integer field with values from 1 to X, where X is, at least the maximum amount of dates that separate the min(day) and max(day) from your table. If you're not sure about that date, just add 100,000 numbers and you'll be able to generate range periods for over 200 years. In the previous query, this table is seq and the column it has is val.

This results in:

+--------------+--------------+|     COL1     |     COL2     |+--------------+--------------+| 1,2012-01-01 | 2012-01-04,4 || 1,2012-01-05 | 2012-01-07,5 || 1,2012-01-08 | 2012-01-10,4 || 1,2012-01-11 | 2012-01-12,5 || 1,2012-01-13 | 2012-01-15,0 || 1,2012-01-16 | 2012-01-19,5 |+--------------+--------------+

Ok, I'm lying. The result is actually returning a counter column. Just disregard it, as removing it (using a derived table) would be even less performant!

and here's a one liner brutality for you :) (Note: Change the "datt" table name.)

select dd.category,dd.day as start_day,(select dp.day from     (        select 1 as n,d1.category,d1.day,d1.a from datt d1 where not exists (            select * from datt where day = d1.day - INTERVAL 1 DAY and a=d1.a        )        union        select 2 as n,d1.category,d1.day,d1.a from datt d1 where not exists (            select * from datt where day = d1.day + INTERVAL 1 DAY and a=d1.a        )    ) dp where dp.day >= dd.day - INTERVAL (n-2) DAY order by day asc limit 0,1) as end_day,dd.a from (    select 1 as n,d1.category,d1.day,d1.a from datt d1 where not exists (        select * from datt where day = d1.day - INTERVAL 1 DAY and a=d1.a    )    union    select 2 as n,d1.category,d1.day,d1.a from datt d1 where not exists (        select * from datt where day = d1.day + INTERVAL 1 DAY and a=d1.a    )) ddwhere n=1

and it's output is :

|| 1 || 2012-01-01 || 2012-01-01 || 4 |||| 1 || 2012-01-03 || 2012-01-04 || 4 |||| 1 || 2012-01-05 || 2012-01-07 || 5 |||| 1 || 2012-01-08 || 2012-01-10 || 4 |||| 1 || 2012-01-11 || 2012-01-12 || 5 ||

Note: Thats the result for non-existing 2012-01-02 in a 01-12 day table.

No need for PHP or temporary tables or anything.

DISCLAIMER: I did this just for fun. This stunt may be too crazy to be used in a production environment. Therefore I'm not posting this as a "real" solution. Also I'm not willing to explain how it works :) And I didn't rethink / refactor it. There might be more elegant ways and names / aliases could be more informative. So please no flame or anything.

Here's my solution. Looks more complicated than it is. I think it may be easier to understand than other answers, no offense :)

Setting up test data:

drop table if exists test;create table test(category int, day date, a int);insert into test values(1       , '2012-01-01' ,   4   ),(1       , '2012-01-02' ,   4   ),(1       , '2012-01-03' ,   4   ),(1       , '2012-01-04' ,   4   ),(1       , '2012-01-05' ,   5   ),(1       , '2012-01-06' ,   5   ),(1       , '2012-01-07' ,   5   ),(1       , '2012-01-08' ,   4   ),(1       , '2012-01-09' ,   4   ),(1       , '2012-01-10' ,   4   ),(1       , '2012-01-11' ,   5   ),(1       , '2012-01-12' ,   5   ),(1       , '2012-01-16' ,   5   ),(1       , '2012-01-17' ,   5   ),(1       , '2012-01-18' ,   5   ),(1       , '2012-01-19' ,   5   );

And here it comes:

SELECT category, MIN(`day`) AS firstDayInRange, max(`day`) AS lastDayInRange, a, COUNT(*) as howMuchDaysInThisRange /*<-- as a little extra*/FROM(SELECT IF(@prev != qr.a, @is_a_changing:=@is_a_changing+1, @is_a_changing) AS is_a_changing, @prev:=qr.a, qr.* /*See if column a has changed. If yes, increment, so we can GROUP BY it later*/FROM(SELECT test.category, q.`day`, COALESCE(test.a, 0) AS a /*When there is no a, replace NULL with 0*/FROMtestRIGHT JOIN(SELECTDATE_SUB(CURDATE(), INTERVAL number_days DAY) AS `day` /*<-- Create dates from now back 999 days. This query is surprisingly fast. And adding more numbers to create more dates, i.e. 10000 dates is also no problem. Therefor a temporary dates table might not be necessary?*/FROM(SELECT (a + 10*b + 100*c) AS number_days FROM  (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) aa, (SELECT 0 AS b UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) bb, (SELECT 0 AS c UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) cc)sq /*<-- This generates numbers 0 to 999*/)q USING(`day`) , (SELECT @is_a_changing:=0, @prev:=0) r/*This WHERE clause is just to beautify. It may not be necessary*/WHERE q.`day` >= (SELECT MIN(test.`day`) FROM test) AND q.`day` <= (SELECT MAX(test.`day`) FROM test) )qr)asdfGROUP BY is_a_changingORDER BY 2

Result looks like this:

category    firstDayInRange     lastDayInRange      a   howMuchDaysInThisRange--------------------------------------------------------------------------1           2012-01-01          2012-01-04          4   41           2012-01-05          2012-01-07          5   31           2012-01-08          2012-01-10          4   31           2012-01-11          2012-01-12          5   2            2012-01-13          2012-01-15          0   31           2012-01-16          2012-01-19          5   4