X-Git-Url: https://git.rvb.name/weathermon.git/blobdiff_plain/e4e1557426f8e5ca10ca616140528939d86426cd..21464a29a442522e1f34fe885f7060f0fecf9a59:/mysql/meteo_routines.sql diff --git a/mysql/meteo_routines.sql b/mysql/meteo_routines.sql index bf72780..5f819b3 100644 --- a/mysql/meteo_routines.sql +++ b/mysql/meteo_routines.sql @@ -128,4 +128,55 @@ DELIMITER ; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; +DELIMITER $$ +CREATE DEFINER=`admin`@`%` PROCEDURE `Reduce`(pUpTo datetime,pRID integer) +BEGIN + declare lNewRID integer; + declare lSeconds integer; + + declare lStart,lEnd timestamp; + + set lNewRID:=pRID+1; + select seconds into lSeconds from reduce_ranges where id=lNewRID; + + delete from tmp_sensor_values; + + insert into tmp_sensor_values(sensor_id,parameter_id,timestamp,value) + select sensor_id,parameter_id,from_unixtime(avg(unix_timestamp(timestamp))),avg(value) + from sensor_values + where timestamp<pUpTo and reduced=pRID + group by sensor_id,parameter_id,floor(unix_timestamp(timestamp)/lSeconds); + + select date(min(timestamp)),date(max(timestamp)) into lStart,lEnd from tmp_sensor_values; + + delete from sensor_values where timestamp<pUpTo and reduced=pRID; + + insert into sensor_values(sensor_id,parameter_id,timestamp,value,reduced) + select sensor_id,parameter_id,timestamp,value,lNewRID from tmp_sensor_values; + + delete from sensors_ranges + where day>=lStart and day<=lEnd; + + insert into sensors_ranges(day,sensor,parameter,timestamp,min,max) + select date(timestamp),sensor_id,parameter_id,max(timestamp),min(value),max(value) + from sensor_values + where timestamp>=lStart and timestamp<date_add(lEnd, interval 1 day) + group by date(timestamp),sensor_id,parameter_id; + + delete from tmp_sensor_values; + +END$$ +DELIMITER ; + +DELIMITER $$ +CREATE DEFINER=`admin`@`%` PROCEDURE `Cron_Reduce`(pMonths integer) +BEGIN + declare lTime datetime; + select date_sub(date_format(now(),'%Y-%m-01'), interval pMonths month) into lTime; + call meteo.reduce(lTime,0); +END$$ +DELIMITER ; + + + -- Dump completed on 2014-09-25 14:45:45