CODEWITHSUNDEEP
mysqldate
mrlayance
mrlayance

Reputation: 663

Create mysql date range

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

Upvotes: 6

Views: 3242

Answers (4)

Nibbik
Nibbik

Reputation: 31

With the new SEQUENCE engine in MariaDB, there is a possibility:

SELECT 
    DATE_ADD(
        CAST('2022-06-01' AS DATE), 
        INTERVAL `s1`.`seq` DAY
    ) AS `dates` 
FROM `seq_0_to_364` AS `s1`;

Upvotes: 0

Paulten
Paulten

Reputation: 59

Realize that this is an old thread- However, I found it useful and would like to suggest adding SET AUTOCOMMIT=0 to Brad's procedure. This will seriously increase the performance (On my system; from 2 hours to 4 seconds). More information is found here:

http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-transaction-management.html

Upvotes: 0

Brad Christie
Brad Christie

Reputation: 101604

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. ;-)

Upvotes: 11

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

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;

Upvotes: 1

Related Questions