How to Efficiently Populate a MySQL Table with a Range of Dates?

MySQL date range query: fill the table with date range

Manually populating a specified date range in a MySQL table can be time-consuming. To automate this process, you can use a stored procedure to insert multiple dates into the table at once.

Solution

The following stored procedure filldates populates a table named tablename with dates ranging between the start date and the end date:

<code class="language-sql">DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO tablename (datecolumn) VALUES (dateStart);
    SET dateStart = dateStart + INTERVAL 1 DAY;
  END WHILE;
END |
DELIMITER ;</code>

To use this procedure, call it with the desired start and end dates:

<code class="language-sql">CALL filldates('2011-01-01','2011-12-31');</code>

This will insert all dates between January 1st and December 31st, 2011 into the tablename table.

Check existing dates

You can enhance this process to check if the date already exists in the table before inserting it:

<code class="language-sql">CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  DECLARE adate DATE;
  WHILE dateStart <= dateEnd DO
    SELECT datecolumn INTO adate FROM tablename WHERE datecolumn = dateStart LIMIT 1;
    IF adate IS NULL THEN
      INSERT INTO tablename (datecolumn) VALUES (dateStart);
    END IF;
    SET dateStart = dateStart + INTERVAL 1 DAY;
  END WHILE;
END;</code>

This will prevent duplicate dates from being inserted into the table.

The above is the detailed content of How to Efficiently Populate a MySQL Table with a Range of Dates?. For more information, please follow other related articles on the PHP Chinese website!