How to populate a table with a range of dates?

Asked 12/4, 2012 at 21:20 Answered 20/6, 2018 at 18:16

I need a MySQL table to hold ALL DATES between 2011-01-01 and 2011-12-31. I have created a table with one column names "_date", type DATE.

With what query can I populate the table with all the desired dates (instead of having to enter them by hand)?

Unexpected answered 12/4, 2012 at 21:20 Comment(4)

and you are using only mysql? nothing like php etc.? – Ciaracibber 12/4, 2012 at 21:25

And to answer your question, I have access to PHP, but (i) wouldn't really know how to quickly calculate all the dates within the range in PHP and (ii) i guess MySQL could do that far more efficient? – Unexpected 12/4, 2012 at 21:37

@hjpotter92: If you know both equally as well, it's really not easier in PHP. And it's a lot more efficient to do it in MySQL. – Lazy 22/10, 2015 at 6:32

Here is an example which uses a simple command to generate the days in the wanted range, and pipes them to sqlite for import. I guess it would be easy to adapt the last part for mysql. – Lola 8/10, 2021 at 14:53

Try this:

DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO tablename (_date) VALUES (dateStart);
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;
|
DELIMITER ;
CALL filldates('2011-01-01','2011-12-31');

Here's the SQL Fiddle to play with it: http://sqlfiddle.com/#!2/65d13/1

EDIT (to check if date already exists) as asked by Andrew Fox.

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)

BEGIN

DECLARE adate date;

    WHILE dateStart <= dateEnd DO

        SET adate = (SELECT mydate FROM MyDates WHERE mydate = dateStart);

        IF adate IS NULL THEN BEGIN

            INSERT INTO MyDates (mydate) VALUES (dateStart);

        END; END IF;

        SET dateStart = date_add(dateStart, INTERVAL 1 DAY);

    END WHILE;

END;//

Here's the SQL Fiddle to play with it: http://sqlfiddle.com/#!2/66f86/1

Luo answered 12/4, 2012 at 21:28 Comment(10)

Didn't work on my server atleast. alter routine command denied to user 'name'@'localhost' for routine 'name.filldates': DROP PROCEDURE IF EXISTS filldates – Ciaracibber 12/4, 2012 at 21:32

A different error this time:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER | CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE) BEGIN WHI' at line 1: DELIMITER | CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE) BEGIN WHILE dateStart <= dateEnd

– Ciaracibber 12/4, 2012 at 21:36

Are you running your own mysql server? If so, try logging in as the root user or even grant the necessary permissions to your current user account. Sample: GRANT ALL ON mydb.* TO 'someuser'@'somehost'; More on this here: dev.mysql.com/doc/refman/5.1/en/… – Luo 12/4, 2012 at 21:37

funnily enough. seems to work on server machine. but not when i am using remote access. – Ciaracibber 12/4, 2012 at 21:42

Yes... that's totally a permission issue. – Luo 12/4, 2012 at 21:43

Haven't tested it mysql yet (will do tomorrow) but assuming it works fine, accepted your answer. Thanks! – Unexpected 12/4, 2012 at 22:39

+1 from me, your code worked better than my expectations. Thank you!! Small question, how would I go about running a check to see if the date already exists? Cheers! – Ameliaamelie 15/11, 2012 at 9:40

@AndrewFox: Updated the answer to get you covered.. :) – Luo 17/11, 2012 at 3:31

To those who are getting syntax errors. If you connect to your database via MySQL Workbench and try to run the create procedure script you will get syntax errors. To get this script to run right click the "Stored Procedures" menu and select "Create Stored Procedure" then paste the create procedure script, remove the "//" in the end of the script and hit "Apply". It worked for me. – Dabchick 5/8, 2015 at 18:41

Wana give you double score up! – Kacykaczer 20/8, 2018 at 7:46

I did not want my SQL query to require external dependencies (needing to have a calendar table, procedure for populating a temporary table with dates, etc.) The original idea for this query came from http://jeffgarretson.wordpress.com/2012/05/04/generating-a-range-of-dates-in-mysql/ which I had slightly optimized for clarity and ease of use.

SELECT (CURDATE() - INTERVAL c.number DAY) AS date
FROM (SELECT singles + tens + hundreds number FROM 
( SELECT 0 singles
UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
) singles JOIN 
(SELECT 0 tens
UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
) tens  JOIN 
(SELECT 0 hundreds
UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
) hundreds
ORDER BY number DESC) c  
WHERE c.number BETWEEN 0 and 364

It is simple to optimize and scale this table for other uses. You can easily get rid of the tens and hundreds tables if you only need one week of data.

If you need a larger set of numbers, it is easy to add a thousands table. You only need to copy and paste the table with hundreds and add a zero to 9 numbers.

EDIT: SQL SERVER version needed minor tweaks

SELECT DATEADD(day, -1 * c.number, CAST(GETDATE() AS DATE)) AS date
FROM (SELECT singles + tens + hundreds number FROM 
( SELECT 0 as singles
UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
) singles CROSS JOIN 
(SELECT 0 tens
UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
) tens CROSS JOIN 
(SELECT 0 hundreds
UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
) hundreds 
) c  
WHERE c.number BETWEEN 0 and 364
ORDER BY date ASC

Anthonyanthophore answered 8/7, 2014 at 3:24 Comment(3)

This is a very interesting concept, a bit slow when processing many dates, but, quite good indeed. Perhaps faster than the alternatives though. – Hyperbole 24/11, 2015 at 15:46

Very clever. If someone is asking himself: c table generated by the inner select the sum between 0-9, 10-90, 100-900 which is a big interval with numbers from 0 to 999 (1000 numbers in total). Outer select is selecting a list of numbers from 0 to 364 (ie 365 days) and select all dates one year up to today (CURDATE). – Loferski 3/4, 2017 at 18:17

Let's say today is 2022-04-08 what if I want calendar range from 2022-01-01 up to 2022-12-31? How to do it? since the date range is historical and future date – Anywhere 8/4, 2022 at 4:35

if you're in a situation like me where procedures are prohibited, and your sql user does not have permissions for insert, therefore insert not allowed, but you want to generate a list of dates in a specific period, say current year to do some aggregation, use this

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_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 gen_date between '2017-01-01' and '2017-12-31'

Bordello answered 1/9, 2017 at 1:39 Comment(4)

This is the cleanest and most concise solution here, but it could be made even more concise: use HAVING instead of WHERE and you can get rid of the outer SELECT wrapper. – Kenny 21/11, 2018 at 12:4

This is freaking awesome – Waterloo 28/2, 2019 at 14:15

Love this, I'm working with google data studio and this is the only way to get dates in one select and without changing database, I found in three days of searching. – Alli 13/6, 2019 at 10:43

I have a DB for which I only have access to views, not the tables themselves, and this is PERFECT. – Corse 4/2, 2021 at 19:33

I found this paste-and-go variant working:

DROP PROCEDURE IF EXISTS FillCalendar;
DROP TABLE IF EXISTS calendar;
CREATE TABLE IF NOT EXISTS calendar(calendar_date DATE NOT NULL PRIMARY KEY);

DELIMITER $$
    CREATE PROCEDURE FillCalendar(start_date DATE, end_date DATE)
    BEGIN
    DECLARE crt_date DATE;
    SET crt_date = start_date;
    WHILE crt_date <= end_date DO
        INSERT IGNORE INTO calendar VALUES(crt_date);
        SET crt_date = ADDDATE(crt_date, INTERVAL 1 DAY);
    END WHILE;
    END$$
DELIMITER ;

CALL FillCalendar('2013-01-01', '2013-01-03');
CALL FillCalendar('2013-01-01', '2013-01-07');

Sawbuck answered 25/10, 2013 at 11:0 Comment(1)

if anyone wants some pi: CALL FillCalendar('2013-01-01', '2099-01-04'); select count(*) from calendar; 31415 – Cloutman 2/3, 2016 at 22:50

I recently had a need to create a calendar_date table as below:

CREATE TABLE `calendar_date` (
    `date`    DATE NOT NULL      -- A calendar date.
    , `day`   SMALLINT NOT NULL  -- The day of the year for the date, 1-366.
    , `month` TINYINT NOT NULL   -- The month number, 1-12.
    , `year`  SMALLINT NOT NULL  -- The year.
    , PRIMARY KEY (`id`));

I then populated it with all possible dates between January 1, 2001 and December 31, 2100 (both inclusive) using the query below:

INSERT INTO `calendar_date` (`date`
    , `day`
    , `month`
    , `year`)
SELECT
    DATE
    , INCREMENT + 1
    , MONTH(DATE)
    , YEAR(DATE)
FROM
    -- Generate all possible dates for every year from 2001 to 2100.
    (SELECT
        DATE_ADD(CONCAT(YEAR, '-01-01'), INTERVAL INCREMENT DAY) DATE
        , INCREMENT
    FROM
        (SELECT
            (UNITS + TENS + HUNDREDS) INCREMENT
        FROM
            (SELECT 0 UNITS 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) UNITS
        CROSS JOIN
            (SELECT 0 TENS UNION
            SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION
            SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION
            SELECT 70 UNION SELECT 80 UNION SELECT 90) TENS
        CROSS JOIN
            (SELECT 0 HUNDREDS UNION
            SELECT 100 UNION SELECT 200 UNION SELECT 300 UNION
            SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION
            SELECT 700 UNION SELECT 800 UNION SELECT 900) HUNDREDS
        ) INCREMENT
        -- For every year from 2001 to 2100, find the number of days in the year.
        , (SELECT
            YEAR
            , DAYOFYEAR(CONCAT(YEAR, '-12-31')) - DAYOFYEAR(CONCAT(YEAR, '-01-01')) + 1 DAYS
        FROM
            -- Generate years from 2001 to 2100.
            (SELECT
                (2000 + UNITS + TENS) YEAR
            FROM
                (SELECT 0 UNITS 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) UNITS
            CROSS JOIN
                (SELECT 0 TENS UNION
                SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION
                SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION
                SELECT 70 UNION SELECT 80 UNION SELECT 90) TENS
            ) YEAR
        WHERE
            YEAR BETWEEN 2001 AND 2100
        ) YEAR
      WHERE
          INCREMENT BETWEEN 0 AND DAYS - 1
      ORDER BY
          YEAR
          , INCREMENT) DATE;

On my local MySQL database, the INSERT query took just a few seconds. Hope this helps someone.

Krone answered 31/1, 2018 at 9:40 Comment(0)

If you have a table with a large enough contiguous set of ids you could use -

INSERT INTO tablename (_date)
SELECT '2011-01-01' + INTERVAL (id - 1) DAY
FROM some_table_with_lots_of_ids
WHERE id BETWEEN 1 AND 365

note: but be aware that this might get you in trouble during leap-years (having 366 days)

Admiration answered 12/4, 2012 at 22:45 Comment(0)

This can be achieved in PHP by using a simple for loop. There are a couple of ways to do it. One way would be to to place the original date in a variable and have the loop run through it for every day by adding +1 day on each loop, for instance, you'll start on 01/01/2011 and then the loop will add 0 the first time, 1 day the next, followed by 2 days so forth and so forth to the $i variable. You could then print out the days or add them to your database. In this case $i would represent the counter with 0 being the starting point, <=365 is how many loops you want to go through which is equal to or less than the number of days and $i++ adds +1 to the $i variable on each loop.

date('Y-m-d' converts the date into yyyy-mm-dd. Using a capital Y gives you a full 4 digit year whereas using a lower case y will give you the last 2 digits of the year. You want to keep it in this order to add it in a date field in mySQL.

strtotime($originalDate parses the date into a Unix time stamp and the ."+".$i." day") basically adds the value of $i in days to the date.

Finally there's the mysqli query. $db represents the database connection variable, this will need to be changed to whatever variable you have set for the connection. This is followed by the actual query. Just exchange the word table for your table name and the date before VALUES to you date row name and you're ready to go.

The following is an example:

<?php
for($i=0;$i<=365;$i++){ 
$originalDate = "01/01/2011";
$date = date('Y-m-d',strtotime($originalDate . "+".$i." day"));
mysqli_query($db, "INSERT INTO table (date)VALUES('$date')");
}

Another way to achieve this using the for function would be to include the strtotime dates directly in the for actions as an oppose to the counter variables, which is an even shorter piece of code. Replace $i=0 (the starting counter point) with the starting day point, follow that with the less than or equal to the end day point (the number of loops) then finally with your plus +1 to the first statement placed into a variable ready for use.

Finally, convert the date into Y-m-d format ready to be placed into the database and run the query.

Again, as with the first example, this can be printed or placed directly into your database.

The following is an example:

<?php
for ($startdate = strtotime("2011-01-01"); $startdate <= strtotime("2011-12-31"); $startdate = strtotime("+1 day", $startdate)) {
$date= date("Y-m-d", $startdate);
mysqli_query($db, "INSERT INTO tracking (date)VALUES('$date')");
}

I've probably made it sound more confusing than it is, but hope it will at least give you an idea on how it works.

Hobbie answered 8/8, 2016 at 1:43 Comment(2)

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, as this reduces the readability of both the code and the explanations! – Cati 8/8, 2016 at 4:50

Sorry about that, it was my first ever post. I've edited the answer to include a description. I've also responded to another who wanted to only add Monday to Friday throughout a year in another post. I think you can find that in my profile... – Hobbie 8/8, 2016 at 11:21

Thanks to IvanD. I've got a better solution which allowes you to create a specified calendar table. For example, if I'm trying to create a table of 2014-04, it looks like this:

SELECT (CURDATE() - INTERVAL c.number DAY) AS DATE
FROM 
(
    SELECT singles + tens + hundreds number FROM 
    ( 
        SELECT 0 singles
        UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
        UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
        UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
    ) singles JOIN 
    (
        SELECT 0 tens
        UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
        UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
        UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
    ) tens  JOIN 
    (
        SELECT 0 hundreds
        UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
        UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
        UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
    ) hundreds
    ORDER BY number DESC
) c  
WHERE c.number BETWEEN 
DAYOFYEAR(NOW()) - DAYOFYEAR('2014-04-01')-  DAY(LAST_DAY('2014-04-01')) +1
AND 
DAYOFYEAR(NOW()) - DAYOFYEAR('2014-04-01')

Backstay answered 19/11, 2014 at 1:50 Comment(1)

This won't return table of 2014-04, but table with already passed days in selected month and in current year! – Sieracki 12/4, 2016 at 12:15

Inspired by IvanD`s great number join i come to this:

SELECT DATE_ADD('2015-10-21', INTERVAL c.number DAY)    AS DATE
FROM 
(
    SELECT singles + tens + hundreds+thousands number FROM 
    ( 
        SELECT 0 singles
        UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
        UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
        UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
    ) singles JOIN 
    (
        SELECT 0 tens
        UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
        UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
        UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
    ) tens  JOIN 
    (
        SELECT 0 hundreds
        UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
        UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
        UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
    ) hundreds
     JOIN 
    (
        SELECT 0 thousands
        UNION ALL SELECT  1000 UNION ALL SELECT  2000 UNION ALL SELECT  3000
        UNION ALL SELECT  4000 UNION ALL SELECT  5000 UNION ALL SELECT  6000
        UNION ALL SELECT  7000 UNION ALL SELECT  8000 UNION ALL SELECT  9000
    ) thousands
    ORDER BY number DESC
) c  
WHERE c.number BETWEEN 
0 
AND
DATEDIFF('2016-10-08', '2015-10-21')

Simla answered 19/7, 2016 at 19:31 Comment(1)

@Leniel Macaferi, could you please help #45491112 – Armoured 3/8, 2017 at 17:36

INSERT INTO my_dates (\`_date\`) SELECT DATE_ADD('2011-01-01', INTERVAL @_tmp:=@_tmp+1 day) \`_date\`
FROM (SELECT @_tmp:=-1 d 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) a /\*10^1\*/
JOIN (SELECT 0 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) b /\*10^2\*/
JOIN (SELECT 0 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) c /\*10^3\*/
WHERE @_tmp+1 BETWEEN 0 AND DATEDIFF('2011-12-31', '2011-01-01');

Kinakinabalu answered 20/6, 2018 at 18:16 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags