MySql create a table - each day a record

Posted on 2011-10-23
Last Modified: 2012-05-12

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
Question by:starhu
    LVL 59

    Accepted Solution

    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.


    Expert Comment

    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.


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API ( …
    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now