How to populate a table with a range of dates?

Question

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)?

Answer 1

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

Answer 2

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.