Ever since we moved into our new apartment we noticed that the relative humidity is very low, sometimes even as low as 20%. This has caused some sparkling moments between my wife and myself…
Depending where you look for information, different ranges are being mentioned as being healthy relative humidity. For now I am regarding the range between 30 and 60 percent as being OK. between 30 and 60 percent seems to be considered a ‘healthy’ relative humidity.
select d.value from sensor_data d , (select max(id) as id from sensor_data where sensor_id = 3) c where d.id = c.id and ( d.value < 30 or d.value > 60 );
Humidity is outside the healthy range (30-60%%). Humidity is %.2f%%
Remove Outlier Records
This one is not so much a rule that would actually require information to be reported but the Rule module provides a convenient method to periodically wipe incorrect and/or suspect data. The main reason for this rule, that from time to time data collected from the DHT22 sensor is not correct as can been seen from these charts. The spikes in the charts are definitely the result of bad data read from the sensors. To make the process easier an new column (and index) has been added to the
sensor_data table, named
valid which will be populated with the value
alter table sensor_data add column valid char(1) not null default 'Y' after value; create index sd_valid_idx on sensor_data(valid);
select x.ct from (select count(*) as ct from sensor_data where valid = 'N' ) x where x.ct > 0;
%d records with suspect data have been removed.
Definition of the
create procedure mark_invalid() begin update sensor_data as a inner join (select id from (select id , (@prev := case when @sens = sensor_id then @prev else null end) as prev , (@sens := sensor_id) sensor , ( value - @prev ) / @prev * 100 as perc , (@prev := value) as curr from sensor_data d , (select @prev := null, @sens := null) a order by sensor_id, id) x where abs(x.perc) > 25) as b on a.id = b.id set a.valid = 'N'; commit; end;
Definition of the
create procedure delete_invalid() begin delete from sensor_data where valid = 'N'; commit; end;