Миграция с OpenWeatherMap на OpenMeteo в связи с отключением бесплатного API с 26...

[weathermon.git] / mysql / meteo_routines.sql

diff --git a/mysql/meteo_routines.sql b/mysql/meteo_routines.sql
index bf72780f001753081afaa6adb04b05ccddc7a6a0..5f819b3f90afaf6db21623827ca65cf3c97b9267 100644 (file)
--- 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 */;
 
 /*!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
 -- Dump completed on 2014-09-25 14:45:45