diff options
| -rwxr-xr-x | test.r | 65 | ||||
| -rw-r--r-- | trigger.sql | 30 |
2 files changed, 68 insertions, 27 deletions
@@ -32,6 +32,28 @@ for (i in seq(1,nrow(brenner))) { } brenner <- brennerBla +# Solar Pumpe +solarEngine <- dbGetQuery(con, + paste("SELECT timestamp as \"time\", + value_int + FROM value + WHERE sensor_id IN (select id from sensor where name = 'heizkreisregler9.solarDaten.solarPumpe') + AND timestamp > now() - interval '1 day' + AND value_int IS NOT NULL + ORDER BY timestamp asc", sep="")) +# reduzieren +solarEngineNew <- table(NULL,NULL) +emptyList <- c() +old_value_int <- "blub" +for (i in seq(1,nrow(solarEngine))) { + if (solarEngine$value_int[i] != old_value_int) { + old_value_int <- solarEngine$value_int[i] + solarEngineNew <- rbind(solarEngineNew, data.frame(time=solarEngine$time[i], value_int=solarEngine$value_int[i])) + } +} +solarEngine <- solarEngineNew + +# Numerische Sensoren Lesen readNumericSensorDay <- function (sensorName) { d <- dbGetQuery(con, paste("SELECT timestamp", @@ -39,22 +61,24 @@ readNumericSensorDay <- function (sensorName) { " FROM value", " WHERE sensor_id IN (select id from sensor where name = '",sensorName,"')", " AND timestamp > now() - interval '1 day'", + # ausreißer ausblenden + " AND COALESCE(value_int,value_float) < 200", + " AND COALESCE(value_int,value_float) > -50", " ORDER BY timestamp desc", sep="")) d$timestamp <- as.POSIXct(round(d$timestamp, "mins")) - d <- supsmu(as.vector(d$timestamp), as.vector(d$value)) + #d <- supsmu(as.vector(d$timestamp), as.vector(d$value)) names(d) <- c("time", "value") -# myMean <- function(values) { -# print(mad(values)) -# if (mad(values) > 1) { -# print(paste("Streiche ",length(values), -# " Werte aus",sensorName, -# "mad =", mad(values))) -# values <- values * NA -# } -# mean(values) -# } -# + myMean <- function(values) { + if (mad(values) > 2) { + print(paste("Streiche ",length(values), + " Werte aus",sensorName, + "mad =", mad(values))) + values <- values * NA + } + mean(values) + } + # dm <- aggregate(d$value, list(time=d$time), myMean) dm <- d dm$time <- as.POSIXct(dm$time,origin=nulltime) @@ -89,6 +113,23 @@ for (i in c(1:nrow(brenner))) { text(as.numeric(brenner$time[i]), max(data[2:length(data)],na.rm=TRUE)/2, paste("ab hier", brenner$value_string[i]) ,srt=90) } +# quick and dirty add solarEngine to plot + +rect_start <- 0 +rect_color <- rgb(255, 255, 0, alpha=40, maxColorValue=255) + +for (i in c(1:nrow(solarEngine))) { + if (solarEngine$value_int[i] == 1) { + rect_start <- solarEngine$time[i]; + } else if (rect_start != 0) { + end <- solarEngine$time[i]; + # plot + rect(rect_start, 0, solarEngine$time[i], 100, col=rect_color,border=F) + beginn <- 0 + } + #text(as.numeric(solarEngine$time[i]), max(data[2:length(data)],na.rm=TRUE)/2, paste("Solar Pumpe", solarEngine$value_int[i]), srt=90) +} + rangeX <- as.POSIXct(range(data$time)) s <- round.POSIXt(seq(rangeX[1], rangeX[2], by=60*60),"hour") axis.POSIXct(1, at=s, format="%H:%M") diff --git a/trigger.sql b/trigger.sql index 0c06a99..fb60617 100644 --- a/trigger.sql +++ b/trigger.sql @@ -20,26 +20,26 @@ DROP FUNCTION value_insert_trigger_func() CASCADE; CREATE FUNCTION value_insert_trigger_func() RETURNS trigger AS $$ BEGIN - DELETE FROM value_cache - WHERE date_trunc('hour', timestamp) = - (SELECT date_trunc('hour', max(timestamp)) FROM value) + DELETE FROM value_cache + WHERE date_trunc('hour', timestamp) = + (SELECT date_trunc('hour', max(timestamp)) FROM value) AND sensor_id = (SELECT sensor_id from value where id = (select max(id) from value)); - INSERT INTO value_cache + INSERT INTO value_cache SELECT - COALESCE(0, (select max(id)+1 from value_cache)) as "id" --XXX use seq - ,date_trunc('hour', timestamp) AS "timestamp" - ,sensor_id AS "sensor_id" - ,type AS "type" - ,AVG(value_float) AS "value_float" - ,AVG(value_int) AS "value_int" - ,first(value_string) AS "value_string" + COALESCE(0, (select max(id)+1 from value_cache)) as "id" --XXX use seq + ,date_trunc('hour', timestamp) AS "timestamp" + ,sensor_id AS "sensor_id" + ,type AS "type" + ,AVG(value_float) AS "value_float" + ,AVG(value_int) AS "value_int" + ,first(value_string) AS "value_string" FROM value - WHERE date_trunc('hour', timestamp) = (SELECT date_trunc('hour', max(timestamp)) FROM value) - AND sensor_id = (SELECT sensor_id FROM value WHERE id = (SELECT MAX(id) FROM value)) - GROUP BY type, sensor_id, date_trunc('hour', timestamp); + WHERE date_trunc('hour', timestamp) = (SELECT date_trunc('hour', max(timestamp)) FROM value) + AND sensor_id = (SELECT sensor_id FROM value WHERE id = (SELECT MAX(id) FROM value)) + GROUP BY type, sensor_id, date_trunc('hour', timestamp); - RETURN NULL; + RETURN NULL; END; $$ LANGUAGE plpgsql; |