Mysql – How to display all the dates between multiple two dates in a table

datejoin;MySQL

I want to display all the dates between two dates for a particular record

And this is the table :

ID Start_Date  End_Date
-------------------------
1  2013-01-14  2013-01-18
2  2013-02-01  2013-02-04

and now i want to get all the dates between from date and to date.

Expected output

ID Date
-------------
1  2013-01-14
1  2013-01-15
1  2013-01-16
1  2013-01-17
1  2013-01-18
2  2013-02-01
2  2013-02-02
2  2013-02-03
2  2013-02-04

guide me to write query for that with out creating any extra tables.

I have already tried this following query

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'

its working fine for single record. but i want get all the date intervals from my table

Updated

i have 6 qty of chairs for all days. So one user book 3 chars on 2013-01-14 to 2013-01-18 another person book 2 chars on 2013-01-17 to 2013-01-20. So my expected output shown below.

ID Date        Available
------------------------
1  2013-01-14          3
1  2013-01-15          3
1  2013-01-16          3
1  2013-01-17          5 
1  2013-01-18          5
1  2013-01-19          2
1  2013-01-20          2 
1  2013-01-21          2

Best Answer

The easiest way is to have a calendar table, defined in the following way:

CREATE TABLE calendar
(
    a_day date PRIMARY KEY
) ;

... and filled with all the relevant dates (i.e.: all days from 1990-1-1 to 2100-12-31). For the sake of simplicity, we will fill it only with year 2013:

INSERT INTO 
     calendar (a_day)
VALUES
    ('2013-01-01'),
    ('2013-01-02'),
    ('2013-01-03'),
    ('2013-01-04'),
    ('2013-01-05'),
    -- everything up to
    ('2013-12-31') ;

At this point, you can just have a JOIN with the two tables; with the join condition not being an equality, but a range condition:

SELECT
     t.id, c.a_day
FROM
     t
     JOIN calendar c ON c.a_day BETWEEN t.start_date AND t.end_date 
ORDER BY
     t.id, c.a_day ;

... and get

id | a_day     
-: | :---------
 1 | 2013-01-14
 1 | 2013-01-15
 1 | 2013-01-16
 1 | 2013-01-17
 1 | 2013-01-18
 2 | 2013-02-01
 2 | 2013-02-02
 2 | 2013-02-03
 2 | 2013-02-04

You can see all the setup at dbfiddle here

Related Question