• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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
1 Solution
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', NumberList.id) 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', NumberList.id) <= '2009-01-13';

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


Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now