mysql - Add rows in between with default values -

i have table values this

i using mysql , first field date (timestamp), second double

09:00:00 xxx 09:01:00 yyy 09:04:00 zzz 

would possible insert adds missing rows (09:02:00, 09:03:00)? in case sql like?

create procedure inserts necessary values temporary table. this:

drop procedure if exists sp_date_range; delimiter $$ create procedure sp_date_range(in startdate datetime, in enddate datetime) begin drop table if exists tmp_date_range; create table tmp_date_range(a_datetime datetime primary key);  set @date := startdate;  while (@date <= enddate) insert tmp_date_range values (@date); set @date := @date + interval 1 minute; end while;  end $$ delimiter ; 

now call start , end dates.

call sp_date_range('2014-03-10 14:00:00', now()); 

now have table this:

mysql> select * tmp_date_range; +---------------------+ | a_date              | +---------------------+ | 2014-03-10 14:00:00 | | 2014-03-10 14:01:00 | | 2014-03-10 14:02:00 | | 2014-03-10 14:03:00 | | 2014-03-10 14:04:00 | | 2014-03-10 14:05:00 | | 2014-03-10 14:06:00 | | 2014-03-10 14:07:00 | | 2014-03-10 14:08:00 | | 2014-03-10 14:09:00 | | 2014-03-10 14:10:00 | | 2014-03-10 14:11:00 | | 2014-03-10 14:12:00 | | 2014-03-10 14:13:00 | | 2014-03-10 14:14:00 | | 2014-03-10 14:15:00 | | 2014-03-10 14:16:00 | | 2014-03-10 14:17:00 | | 2014-03-10 14:18:00 | | 2014-03-10 14:19:00 | +---------------------+ 

now insert missing values this:

insert your_table (your_timestamp_column) select a.a_datetime tmp_date_range left join your_table b on a.a_datetime = b.your_timestamp_column b.your_timestamp_column null; 

note though, i'm assuming other columns in table nullable. otherwise you'll have specify default value.