Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create mysql date range

Tags:

date

mysql

Can't seem to find the answer I am looking for.

I want to create a range of dates from 2010-11-01 to 2015-01-01 in a table.

2010-11-01 2010-11-02 2010-11-03 etc...

Column datatype is 'Date'

Thanks

like image 799
mrlayance Avatar asked Jan 19 '11 14:01

mrlayance


2 Answers

DROP PROCEDURE IF EXISTS datespopulate;
DELIMITER |
CREATE PROCEDURE datespopulate(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO datetable (d) VALUES (dateStart);
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;
|
DELIMITER ;
CALL datespopulate('2010-11-01','2015-01-01');

Note I named my table "datetable" and the column is named "d", but feel free to change this. Works fine on my end, let me know if you run in to an issue.

Kudos to Joe for getting the ball rolling. ;-)

like image 139
Brad Christie Avatar answered Sep 22 '22 01:09

Brad Christie


You could certainly take the brute force approach.

set @d = cast('2010-11-01' as date);

while (@d < '2015-01-02') do
    insert into YourTable 
        (YourColumn)
        values
        (@d);

    set @d = date_add(@d, interval 1 day);
end while;
like image 34
Joe Stefanelli Avatar answered Sep 21 '22 01:09

Joe Stefanelli