inserting large number of dates

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-3开发者_如何学运维1" inclusive.

Is there any sql query for the above?

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

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

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

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.

The pure MYSQL way of doing this is:

select * from (
select  date('2009-01-01') + interval (@count := @count +  1 )-1 day d
from (select @count := 0) count
cross join (select 1 union all select 1) as two
cross join (select 1 union all select 1) as four
cross join (select 1 union all select 1) as eight
cross join (select 1 union all select 1) as sixteen
cross join (select 1 union all select 1) as thirty_two
cross join (select 1 union all select 1) as sixty_four
cross join (select 1 union all select 1) as one_two_eight
cross join (select 1 union all select 1) as two_five_six
cross join (select 1 union all select 1) as five_twelve
cross join (select 1 union all select 1) as one_thousand_twenty_four) d
where d between '2009-01-01' and '2010-12-31';

and its super fast as well!