script to delete wrong temperature readings

[pgit]

Hi,

I use 1-wire sensors to read the temperature from a solar boiler and a buffer. But once or twice a day they do a misread and insert a value of 0 degrees into the data. I tried to avoid this by resetting the devices frequently, but that doesn't seem to help.
Now If I don't delete these values this messes up the the temperature range and averages used on weekly, monthly and yearly graphs as well.
The only way I know to delete these "0" values is manually, by shift-left clicking the respective values in the daily graph, but this only works in a desktop browser and if I skip a day I will have to delete a whole days data in the monthly graph.
It would be better to cron a php (or other type of) script daily that uses an sql query to delete all values of 0 in the (right) tables with the data of certain devices.
I'm no programmer and no expert on Linux and SQL though, so I'll definitely need some help with this...
Anyone?

Thanks in advance,

Peter

[waaren]

once or twice a day they do a misread and insert a value of 0 degrees into the data.
It would be better to cron a php (or other type of) script daily.

This bash script will do it.

Code: Select all

#!/bin/bash

#
# Clean temperature table by deleting rows where temperature = 0
# requires 
#  	sqlite3 (sudo apt install sqlite3)
#   sufficient rights to the domoticz database
#
# script should be scheduled to execute shortly before 0:00
#

cd /home/pi/domoticz # change to location of your domoticz database

devices2Clean=(3 13 32 32 33 34 42 43 46) # list of ID's of devices to clean (Change for your situation !!)

before=$(sqlite3 domoticz.db "select count(*) from temperature")

for id in ${devices2Clean[@]} 
do 
	sqlite3 domoticz.db "delete from temperature where devicerowid = $id and temperature = 0 " 
	echo cleaned device $id
done

after=$(sqlite3 domoticz.db "select count(*) from temperature")

deleted=$(sqlite3 domoticz.db "select $before - $after from temperature limit 1") 

echo Total number of deleted records is $deleted