MySql create a table - each day a record


I need to create a table (or insert in a table) as follows
- I know the starting date and ending date e.g. 2011.10.12  and 2011.10.16
- I should generate a record for each day between the two dates.
For example the output of the example above would be:


Of course in the real situation the dates can be for example from 2007.01.01 to 2010.10.12 so I can't do this manually.

Thank you
Who is Participating?
Kevin CrossChief Technology OfficerCommented:
If you have a table of numbers, then this can be achieved easily.
Ensure to add in an extra row with 0 to make table of date scenarios a little easier, then the formula example from the Article could look like this in your case:
select date_add(str_to_date('2007.01.01','%Y.%m.%d'), interval n day) as dt
from util.numbers
where n between 0 and datediff('2010.10.12', '2007.01.01')

Open in new window

Note the techniques shown to generate the table of numbers in the first place can be adjusted to generate your table of dates.

The following query will give you the expected result.

CREATE TABLE number (num_value INT);
INSERT INTO number (num_value) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT ADDDATE('2009-01-01', AS `DateBetweenRange` FROM
(SELECT n1.num_value + n10.num_value*10 + n100.num_value*100 AS Id
   FROM number n1 CROSS JOIN number AS n10 CROSS JOIN number AS n100) AS NumberList
WHERE ADDDATE('2009-01-01', <= '2009-01-13';

I can make this into a procedure if you want, which will avoid the table creation.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.