double / tripple .. entries

i t looks to me that the sql logger stores entries several times, especially from devices with multiple properties: e.g. a query against a thermostat results in ~70% double / triple entries for temperature with the same date/timestamp and same value. I guess this is due to the fact that some of the updates from the thermostat come in independently and each of the changes triggers a save of all properties.

If the double / triple would be 1-5 % I would understand but 70% waste!

Anything one can do about it , i.e. a clean up sql script? ... that is well beyond my current SQL level.


Karl

[matt (support)]

Hi Karl,

Are the entries really identical? The SQL logger plugin should only be writing entries: 1) on first launch of the Indigo Server, or 2) if a device state has changed.

here the data: just some records:

115984 2014-01-30 01:44:24 67.8
115983 2014-01-30 01:44:24 67.8
115982 2014-01-30 01:38:40 67.6
115981 2014-01-30 01:38:40 67.6
115980 2014-01-30 01:32:57 67.8
115979 2014-01-30 01:32:57 67.8

and here the SQL statement

sqlite3 -header -column indigo_history.sqlite "SELECT id, datetime(ts,'localtime'), temperature FROM device_history_1427925925 ORDER BY id desc"

It was not the thermostat, it is the RFXcom temperature sensor.
out of 29096 only 13728 are unique

Karl

[matt (support)]

But you aren't selecting all the columns. I suspect there is some state/property/etc. in there that is indeed changing. It just isn't the temperature.

Code: Select all

115932 2014-01-30 06:01:12  67.5 F / 4%   4 2014/01/30 00:00:29  4   67.5 4 67.3 67.3  10 normal 6 1                                     
115931 2014-01-30 06:01:12  67.5 F / 4%   4 2014/01/30 00:00:29  4   67.5 4 67.5 67.3  10 normal 6 1                                     
 
115930 2014-01-30 06:00:31  67.5 F / 4%   4 2014/01/29 23:59:47  4   67.5 4 67.5 67.5  10 normal 6 1                                     
115929 2014-01-30 06:00:31  67.5 F / 4%   4 2014/01/29 23:59:47  23 67.5 4 67.5 67.5  10 normal 6 1
                                     
115928 2014-01-30 06:00:31  67.5 F / 4%   4 2014/01/29 23:59:47  23 67.5 3 67.5 67.5  10 normal 6 1                                     
115927 2014-01-30 06:00:31  67.5 F / 4%   4 2014/01/29 23:59:47  23 71.2 3 67.5 67.5  10 normal 6 1 


here with Select * some records ... and yes there is always one number different..
BUT why are there 2 entries with the same time stamp? Should that not go together into one record and take the values of the latest update?


always learning..


Thanks

Karl

[matt (support)]

The changing of the devices states (or other properties) are individually atomic, each one causing the Indigo Server to send out a broadcast that the device changed. The SQL Logger plugin could try to be smarter about coalescing changes that occur within about a second, but note it isn't as simple as just comparing the timestamp since a state going from ON->OFF->ON all within the same second still needs to be logged as at least 3 entries.

Karl, there is a fairly easy solution in your case - simply select using the DISTINCT keyword to eliminate the duplicates. Assuming this is for the graphing plugin, as long as you have that timestamp column in there retrieving only the distinct records should be safe to do.

Adam

Adam, Matt
I understand (a) complexity of catching this while population the DB and (b) the solution.
It is just an observation. Is this issue due to the plugin that generates the data?
i.e. updating states one by one instead of update all states in one command?

Thx

Karl

[matt (support)]

It is more of a server limitation -- there isn't a way to update multiple states with a single API call currently.