Database tips (part 2)

Yves911

There is already a thread about the DB explaining how to reduce size of the DB http://forum.pimatic.org/topic/14/database-tips
Now you may want to “purge” some “not relevant” data, for example if one of your sensors report from time to time crazy values.
In the example below i am going to delete everything greater than 4 for a given deviceId/attributeName

Stop pimatic and MAKE A BACKUP

then

sqlite3 pimatic-database.sqlite

Check which values you are going to delete

select * FROM attributevaluenumber
where CAST(value as integer) > 4 and deviceattributeid in 
(
select id from deviceAttribute
where deviceId = 'consogaz' and attributeName = 'cons_gaz_inst'
);

… and then delete

delete FROM attributevaluenumber
where CAST(value as integer) > 4 and deviceattributeid in 
(
select id from deviceAttribute
where deviceId = 'consogaz' and attributeName = 'cons_gaz_inst'
);

Quit sqlite using ctrl+d, that’s it.