inserting large number of dates

Question

How can I insert all dates in an year(or more) in a table using sql

My dates table has following structure

dates(date1 date);

Suppose I want to insert dates between "2009-01-01" to "2010-12-31" inclusive.

Is there any sql query for the above?

Answer 1

Dates on a table!

Seriously though, I don't know a pure mySQL way to do this (but would be interested to see whether there is one.)

In PHP:

$start_timestamp = strtotime("2009-01-01 00:00:00");
$end_timestamp =   strtotime("2009-12-31 23:59:59");

// $interval can be anything strtotime() can handle, 2 days, 1 week, 6 hours... 
$interval =        "1 day";  

$timestamp = $start_timestamp;

while ($timestamp < $end_timestamp)
 {
   $query = "INSERT INTO tablename (date_column)".
            " VALUES (".date("Y-m-d", $timestamp).");";
   echo $query."<br>";
   // execute query here....
   $timestamp = strtotime("+ ".$interval, $timestamp);
  }

Answer 2

Well, you can do a simple loop:

$start = mktime(12,0,0,1,1,2009);
$end = mktime(15,0,0,12,31,2009);

$values = array();
for($time = $start; $time <= $end; $time += 86400) {
    $values[] = date('Y-m-d', $time);
}

$query = "INSERT INTO dates(`date`) VALUES ('".implode("'), ('", $values)."')";

mysql_query($query);

Answer 3

A pure mysql solution using a stored procedure and REPEAT UNTIL:

delimiter //

CREATE PROCEDURE insert_many_dates(number_to_insert INT)
BEGIN
  SET @x = 0;
  SET @date = '2010-01-01';
  REPEAT 
      SET @x = @x+1;
      INSERT INTO your_table(your_column) VALUES(@date);
      SET @date = DATE_ADD(@date, INTERVAL 1 day);
  UNTIL @x > number_to_insert END REPEAT;
END
//
delimiter ;

Call it by

CALL insert_many_dates(1000);

Answer 4

I know of a simple query that works for Oracle, but I have no idea if it will work on mySQL

insert into DATES_TABLE (
       select sysdate+level-1 as a_date 
       from dual 
       connect by level <= 365)

Just replace sysdate with an argument that contains the date you want to start from and replace 365 with the number of days you want to look forward.

Hope this helps. I didn't test its performance thoroughly but it should be very quick.