X-Git-Url: https://git.rvb.name/weathermon.git/blobdiff_plain/42bd3e809b0fa95d3a463462e242caf40d98bf79..afdf338bb671cf6bdb1bd836fd68cf5ee30b614c:/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