Loop through mySQL columns and average the values

[kebo]

Hey all,
I'm using a MySQL server.

I have a table with a time stamp primary column and columns 1..N containing floats. Every 5 second a new row is added to this table with values from sensors in the real world. Every minute I need to check this table and if the oldest row is more that four hours old, I need to average 1 minutes worth of old data into a single row and stuff that row into a different table. The old rows then need to be deleted. Now I know how to do all of this in VB, c# or even php, but I'd like it to be handled by the server using a procedure if possible. My biggest problem (other than not being extremely fluent in sql) is that I've never dealt with stored procedures before.

So anyway the gist is that this procedure needs to start with the oldest row and if it is more that 4 hours old, the last 1 minute of data from each column needs to be averaged and removed. The averages the get inserted into another table using the oldest row's time stamp. This repeats until the oldest row is not more than 4 hours old.

I know how to get the column names, and I know how to average the data in the range I need, but I don't know how to put it all together. I'm sure I could fumble through this for a couple of hours (ahem...days) and come up with a hodge-podge of a hack that would work, but I really don't want to. I'd rather see how it's done right first.

Can anyone lend a hand to show me how to approach this?
thanks for looking
kevin

[digitalShaman]

stored procedures are not my bag and i have done only very Little with mySQL.
But maybe the following gets you started even though it is MSSQl Syntax:

	declare @dtOldest as datetime 
	set @dtOldest= (select min(dt) from tbl)
	while datediff(hour,@dtOldest,getdate())>=4
		begin
			declare @dtNewOldest as datetime=dateadd(minute,1,@dtOldest)
			INSERT INTO tblNew 
				SELECT min(dt),avg(Value1),avg(Value2)
				FROM tbl
				WHERE dt<=@dtNewOldest
			DELETE FROM tbl WHERE dt<=@dtNewOldest
			SET @dtOldest=@dtNewOldest
		end

its untested so i am not sure if it really does what is asked.

[kebo]

Thanks for that dS. Your help is definitely appreciated.

As is, it doesn't run under MySQL, but I'm fumbling with it to get it to run under MySQL.