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

MySql create a table - each day a record

Hello,

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:

2011.10.12
2011.10.13
2011.10.14
2011.10.15
2011.10.16

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
0
starhu
Asked:
starhu
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
If you have a table of numbers, then this can be achieved easily.
http://www.experts-exchange.com/Database/MySQL/A_3573-A-MySQL-Tidbit-Quick-Numbers-Table-Generation.html
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.

Kevin
0
 
skillszoneCommented:
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.


0

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