mysql - Noon Hour Observation -

i have table number of rows follows:

+---------------------+------+ | utc                 | temp | +---------------------+------+ | 2011-01-30 00:00:14 |   -3 | | 2011-01-30 00:40:06 |   -4 | | 2011-01-30 01:00:15 |   -4 | | 2011-01-30 01:20:14 |   -4 | | 2011-01-30 02:00:12 |   -4 | | 2011-01-30 02:20:18 |   -4 | | 2011-01-30 03:00:16 |   -4 | |         ...         |  ... | 

utc of type datetime, , temp of type int.

for each day, find temp value closest day's noon hour. possibly resulting in table looks this:

+---------------------+------+ | utc                 | temp | +---------------------+------+ | 2011-01-30 12:01:14 |   -3 | | 2011-01-31 11:58:36 |   -4 | | 2011-02-01 12:00:15 |   -5 | | 2011-02-02 12:03:49 |   -7 | | 2011-02-03 02:00:12 |   -8 | |         ...         |  ... | 

finding single day easy enough:

select utc, temp table date(utc)='2011-01-30' order abs(timestampdiff(second,utc,date_add(date(utc),interval 12 hour))) limit 1; 

but somehow doing every day @ same time proving deal more challenging.

(note, there may more values temp in table.)

i use stored procedure:

delimiter $$ create procedure get_temps(d0 date, d1 date) begin     declare d date;     declare done tinyint default 0;     declare cur_dates cursor         select distinct date(utc) date          `table`         date(utc) between d0 , d1;     declare continue handler not found         set done = 1;     -- create table store data     create table if not exists tbl_temp_data (         utc datetime,         temp int     );      open cur_dates;     temp_filter: while done=0         fetch cur_dates d;         if done = 0             insert tbl_temp_data             select                  utc, temp                               `table`                               date(utc)=d             order                  abs(timestampdiff(second,utc,date_add(date(utc),interval 12 hour)))              limit 1;          end if;     end while; end $$ delimiter ;