mySQL - minute data has missing minutes

I have a database with a cityname table and a moisture table. Details are as follows:

'cityname' has 2 columns:  
-city_ID <- integer and primary key that increments automatically  
-city_full_name <- character name i.e. boston, toronto, new york city etc...  

'citymoisture' has 7 columns:  
-city_ID <- tied to the city_ID field via a Foreign Key  
-date  
-time  
-open  
-high  
-low  
-close  
开发者_如何学C

I uploaded the data into the db using the following commands

mysql> LOAD DATA INFILE     'Boston 1 Minute Moisture.txt'
    -> INTO TABLE           moisture
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (@date, @time, open, high, low, close)
    -> SET                  city_id=4,
    ->                       date=STR_TO_DATE(@date, '%m/%d/%Y'),
    ->                       time=STR_TO_DATE(@time, '%h:%i:%s %p');

The problem that I have discovered is that there are gaps in the data. The sensor sometimes fails to save when the minute cycle comes around and so there are periods when the time between 2 successive rows is greater than 1 minute. I want to identify those gaps and insert my own data i.e. ideally a carbon copy of the previous minute's data.

The sample output of the data file:

Date, Time, Open, High, Low, Close  
1/4/1999,9:31:00 AM,0.943,0.943,0.943,0.943  
1/4/1999,9:32:00 AM,0.943,0.943,0.943,0.943
1/4/1999,9:33:00 AM,0.943,0.943,0.943,0.943
1/4/1999,9:35:00 AM,0.943,0.943,0.943,0.943
1/4/1999,9:36:00 AM,0.943,0.943,0.943,0.943

See in this example the data for 9:34 is missing. What I want to do is find a way to indentify where this is happening and make changes. I think I might just copy the data in the previous minute into the missing minute row.

Something like If time[1] - time[0] > 1 then insert the previous row in between. Your help would be greatly appreciated.

Create a table with all of the minutes in it (1440 rows) and do a left outer join to your data table after you've loaded it. Any rows that are null are missing data.

Don't forget the indexes.