Missing data in SQL database

Posted on
Tue Jan 17, 2017 8:41 am
kallovsky offline
Posts: 5
Joined: Dec 27, 2016

Missing data in SQL database

Hi!

I'm new to Indigo and I have problems understanding/using the SQL logger:

I have an Aeotec Multisensor 6 giving me temperature and humidity values every 5 minutes (I can see those in the event log) and I want exactly those values logged in the SQL database. In the logger's configuration I checked the

'Device state history: Store device state changes in the database'

and didn't check

'Changed states only: Store only device states that change...'

Unfortunately, it looks like the data in the database are only taken when the value is changing - which (from my understanding) is the behaviour resulting from the second checkbox. Am I missing something?

Thanks for your help,
k.

Posted on
Tue Jan 17, 2017 12:13 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Missing data in SQL database

how do you test = could you post your sql statement?

Posted on
Tue Jan 17, 2017 2:44 pm
kallovsky offline
Posts: 5
Joined: Dec 27, 2016

Re: Missing data in SQL database

I used
Code: Select all
sqlite3 -header -column indigo_history.sqlite "SELECT * FROM device_history_865130460;"

and this is a typical output (as I said, data are coming in every 5min!):
Code: Select all
2614        2017-01-17 14:13:16  21.8         21.8 °C
2615        2017-01-17 15:08:32  21.7         21.7 °C
2616        2017-01-17 15:18:35  21.8         21.8 °C
2617        2017-01-17 15:28:38  21.7         21.7 °C
2618        2017-01-17 16:03:49  21.8         21.8 °C
2619        2017-01-17 16:28:57  21.9         21.9 °C
2620        2017-01-17 16:33:58  21.8         21.8 °C
2621        2017-01-17 16:39:00  21.9         21.9 °C
2622        2017-01-17 17:14:11  22.0         22.0 °C
2623        2017-01-17 18:54:41  21.9         21.9 °C
2624        2017-01-17 20:00:01  21.8         21.8 °C

I just tried to reinstall the plugin - but doesn't help...

Posted on
Tue Jan 17, 2017 4:20 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Missing data in SQL database

I guess this is a question to mat and jay.


Sent from my iPhone using Tapatalk

Posted on
Tue Jan 17, 2017 7:13 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: Missing data in SQL database

The SQL Logger plugin only adds rows to the table when a device state changes. The Changed states only checkbox changes how many states/columns are logged in each row. When it is disabled if any state changes the row will be written with all state values (the one that changes, and all the others). If the checkbox is enabled, then only the state(s) that change are added to the new rows resulting in a more compact table.

There isn't a way to have the SQL Logger plugin log all received values – it only triggers off of state changes. There is a low level Z-Wave command received hook you can patch into via a custom plugin if you wanted to try that route. It would require a bit of work though to parse out the temperature values from the raw commands.

Image

Posted on
Wed Jan 18, 2017 4:24 am
jh71283 offline
Posts: 127
Joined: Jun 16, 2014

Re: Missing data in SQL database

You could give this plugin a go:

http://forums.indigodomo.com/viewtopic.php?f=134&t=17682
You would need to create a variable that contains your temperature, and a trigger to keep it up to date.

The data then gets logged to graphite every second, to make the graphing easier.

Posted on
Wed Jan 18, 2017 7:00 am
kallovsky offline
Posts: 5
Joined: Dec 27, 2016

Re: Missing data in SQL database

Thanks for clarifying, Matt!

However, I do find this logger behavior really strange: Logging a received value with time stamp _is_ an important information even if the sensor value didn't change - and I would like to collect these data!

I was already wondering why the plotting plugins I tried (matplotlib and indigoplotd) use their own database and don't just plot the SQL data points. I would guess that the graphite data logger works similar - I don't want an extreme oversampling (1-s-sampling of data coming in every 5min) producing unsynchronized, artificial data points.

Matt, would it be a problem to change the logger's behavior and add the option to store all data? Am I really the only one who would like that?

Posted on
Wed Jan 18, 2017 8:33 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Missing data in SQL database

I believe it is easier to create the values that you want instead of increasing the load on the sql server.
Writing to the server works ok if you have some devices/states. I have about 300 devices times 20 states and 200 variables. That increase the size of the database
My problem is the other way around trying to reduce the growth of the dB




Sent from my iPhone using Tapatalk

Posted on
Wed Jan 18, 2017 8:38 am
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: Missing data in SQL database

kallovsky wrote:
Matt, would it be a problem to change the logger's behavior and add the option to store all data? Am I really the only one who would like that?

Unfortunately, it isn't just the logger that would require the change so it would non-trivial.

Image

Posted on
Wed Jan 18, 2017 12:56 pm
DaveL17 offline
User avatar
Posts: 6742
Joined: Aug 20, 2013
Location: Chicago, IL, USA

Re: Missing data in SQL database

kallovsky wrote:
I was already wondering why the plotting plugins I tried (matplotlib and indigoplotd) use their own database and don't just plot the SQL data points.

For matplotlib, my design scheme was based off my individual desire to display simple charts using a nice even time series--for example, plot the outside temperature every 15 minutes. So rather than try to figure out which SQL observation was closest to my 15 minute requirement, or try to interpolate "missing" data, I found it easier to create the data that exactly met my needs. An added bonus was to not have to try to rely on the installation and maintenance of something I don't control (I didn't want to try to do technical support for SQL either to be honest). But the best part on the data side for matplotlib is that if something does go wrong that we can't fix, we can just delete the one data file and the plugin will start a new data file automatically.

I came here to drink milk and kick ass....and I've just finished my milk.

[My Plugins] - [My Forums]

Posted on
Wed Jan 18, 2017 4:30 pm
kallovsky offline
Posts: 5
Joined: Dec 27, 2016

Re: Missing data in SQL database

OK - so I am trying the following atm:

I created a variable Temperature which is updated by a trigger Z-Wave Command Received, Incoming Command Received from Device. The trigger inserts the state of the temperature sensor into the variable and tells the thingspeak plugin to upload data to the server. This seems to work (with only a few seconds offset) and comes close to what I had in mind (although I'm still surprised that there's no built-in data management with exact time stamps :-) ... ).

Dave, you are right, as long as the SQL library only stores infrequent data points, that solution is much easier! I will try the thingspeak solution for a while (Thanks for that plugin, too!) - and see how that works out!

Thank you all for your help,
k.

Posted on
Wed Jan 18, 2017 4:44 pm
DaveL17 offline
User avatar
Posts: 6742
Joined: Aug 20, 2013
Location: Chicago, IL, USA

Re: Missing data in SQL database

kallovsky wrote:
OK - so I am trying the following atm:

I created a variable Temperature which is updated by a trigger Z-Wave Command Received, Incoming Command Received from Device. The trigger inserts the state of the temperature sensor into the variable and tells the thingspeak plugin to upload data to the server. This seems to work (with only a few seconds offset) and comes close to what I had in mind (although I'm still surprised that there's no built-in data management with exact time stamps :-) ... ).

Dave, you are right, as long as the SQL library only stores infrequent data points, that solution is much easier! I will try the thingspeak solution for a while (Thanks for that plugin, too!) - and see how that works out!

Thank you all for your help,
k.

I don't know exactly how you're using the Thingspeak plugin (you're very welcome) but the plugin supports both remote upload as well as a local Thingspeak server. I know there is at least one user that's used the plugin for a local install.

Dave

I came here to drink milk and kick ass....and I've just finished my milk.

[My Plugins] - [My Forums]

Posted on
Wed Jan 18, 2017 5:04 pm
kallovsky offline
Posts: 5
Joined: Dec 27, 2016

Re: Missing data in SQL database

I'm currently using the Mathworks' Thingspeak server and the upload seems to work without any problems. Will play with some custom visualizations in the next days...

Just two suggestions for your plugin: It would be great to be able to set the upload interval to only manually and to be able to manually update individual channels, not only all channels!

Posted on
Wed Jan 18, 2017 9:22 pm
DaveL17 offline
User avatar
Posts: 6742
Joined: Aug 20, 2013
Location: Chicago, IL, USA

Re: Missing data in SQL database

kallovsky wrote:
I'm currently using the Mathworks' Thingspeak server and the upload seems to work without any problems. Will play with some custom visualizations in the next days...

Just two suggestions for your plugin: It would be great to be able to set the upload interval to only manually and to be able to manually update individual channels, not only all channels!

Great suggestions. I'll add both those to the list for a future update.

Cheers!
Dave

I came here to drink milk and kick ass....and I've just finished my milk.

[My Plugins] - [My Forums]

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 1 guest