Page 1 of 1
Ability to ignore 0 values
Posted: Tuesday 17 January 2017 22:51
by Calzor Suzay
Some sensors I have as part of scripts or other sometimes crap out over the internet or the requesting site doesn't respond etc. so you can end up with a 0 value which messes up graphs.
I've tried some logic in scripts or this and that which help but still can end up with 0 values.
I know you can so the shift left click and remove but means you have to check daily the validity of each sensors recordings.
It would be nice to have an option to dump 0 values say once an hour or a defined value if this is possible?
Re: Ability to ignore 0 values
Posted: Wednesday 18 January 2017 8:03
by Egregius
If the 0 value comes from a script you should check it there. For sure if you retrieve data from a url.
If you really want to remove them from the database you could set up a script and execute it by cron.
I run this script every night to remove all data older than 1 hour ( I don't care about that ) and remove all data from unused devices.
You could alter the query to remove 0 values.
Code: Select all
<?php
$db = new SQLite3('/volume1/@appstore/domoticz/var/domoticz.db');
$clean = strftime("%G-%m-%d %k:%M:%S",time()-3600);
$tables = array( 'LightingLog',
'MultiMeter',
'MultiMeter_Calendar',
'Meter',
'Meter_Calendar',
'Percentage',
'Percentage_Calendar',
'Rain',
'Rain_Calendar',
'Temperature',
'Temperature_Calendar',
'UV',
'UV_Calendar',
'Wind',
'Wind_Calendar');
foreach($tables as $table)
{
$query=$db->exec("DELETE FROM $table WHERE DeviceRowID not in (select ID from DeviceStatus where Used = 1) OR Date < '$clean'");
if ($query)
{
$rows = $db->changes();
if($rows>0)
echo $rows." rows removed from $table<br/>";
}
}
$query=$db->exec("DELETE FROM LightingLog WHERE DeviceRowID not in (162)");
if ($query)
{
$rows = $db->changes();
if($rows>0)
echo $rows." rows removed from LightingLog<br/>";
}
$sql = 'VACUUM;';
if(!$result = $db->exec($sql))
die('There was an error running the query [' . $db->error . ']');
Re: Ability to ignore 0 values
Posted: Thursday 19 January 2017 1:09
by Calzor Suzay
I guess I need to know my tables and stuff but doable, thanks.
I'll try and get better error checking in the scripts but this is an option
Re: Ability to ignore 0 values
Posted: Thursday 19 January 2017 11:45
by Egregius
The script runs for over a year daily here. I also use adminer do directly access the database. Never had an issue with the DB.