Back to the Future - Storing historical data in SQL Logger

Posted on
Thu Jan 04, 2018 9:27 pm
InsteonDiego offline
Posts: 48
Joined: Dec 07, 2016

Back to the Future - Storing historical data in SQL Logger

I am currently working on a plugin that gathers data from a REST API, digests it and updates relevant device states. The API also provides historical data, which I am also digesting, but can't quite figure out how to properly store in SQL Logger. Specifics follow, any pointers appreciated!

The data being collected is realtime production from a solar system. To this point, everything is working properly. On the hour, the API also makes available total production for the last hour (same for day, month and year). This data is easily stored in device states, however the problem occurs when trying to graph the data using for example INDIGOPlotD. While the data is historical, the timestamp is current (so for example, production for 10am would appear as an entry at just past 11am). Clearly, the impact increases as the interval gets larger (i.e. we don't want summary data from the year 2017 to show up as 2018).

Is there a way to insert this data into the SQL Logger with an adjusted ts value for the hour [day, month, year] before?

Thanks!

Posted on
Thu Jan 04, 2018 10:29 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Back to the Future - Storing historical data in SQL Logg

That’s an interesting one.
In indigoplotd you can shift the data left / right by days/hours


Sent from my iPhone using Tapatalk

Posted on
Thu Jan 04, 2018 10:39 pm
InsteonDiego offline
Posts: 48
Joined: Dec 07, 2016

Re: Back to the Future - Storing historical data in SQL Logg

Thank you Karl! Love your plugin! I saw that feature and explored it a bit, but it seemed like I could only move the graph forward an hour (10am displays as 11am) and not backwards (11am displays as 10am). Perhaps I missed the proper setting? I thought about simply adding up all the real time data via sum and plotting that, however 1) I know it won't be as accurate as the API due simply to my sampling interval and 2) seems like a lot of unnecessary computation (especially as the sample size gets larger) when the cumulative data is so readily available.

Posted on
Fri Jan 05, 2018 6:09 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Back to the Future - Storing historical data in SQL Logg

what I meant is shift each line feature, but that only works for +x Days, useful for overlaying yesterdays data on today

you can cerate your own data. The plugin can read ";" delimited file; the format:
xcoulumnname;y1name;y2name;y3name..
xcolumn;y1;y2;y3;..
xcolumn;y1;y2;y3;..
xcolumn;y1;y2;y3;..
xcolumn;y1;y2;y3;..

for time stamp the use
20180101140506
for 2018 jan 01 14:05:06

then
in Datasource for lines choose Data entered in file below
in filename or variable name enter the filename of the data
in xscale .. format string enter %Y%m%d%H%M%S

but you need to have ALL data for the plot in that file. no mixing of data w regular data.. still on my to do list

Karl
Last edited by kw123 on Fri Jan 05, 2018 12:26 pm, edited 1 time in total.

Posted on
Fri Jan 05, 2018 9:04 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Back to the Future - Storing historical data in SQL Logg

back to the logger question.. I believe - and Matt/Jay correct me if I am wrong . indigo simply stores
in table device_history_deviceId: seq Id, currentTimestamp + value for each statename
When a state changes.

Changing that would be very involved within indigo.
Adding entries on your own into these tables can easily screw things up
Adding additional tables with the data will be outside indigo device info and hence not usable (at least in indigigolptd as it uses indigo device / variables info to query the sq db)

So your best path forward would be to store the data in a simple ";" separated file. I helped John from the Netherlands to do that for his sensors, and there is an example in the postings . He has data for ~ 2 years from many sensors.

What I need to to is to enable mixing of indigo device/state data + extra data in the data file. right now it is either indigo data or separate file. the challenge is that these 2 can have different x axes. That makes its a little challenging, but thats what it is about


Karl

Posted on
Fri Jan 05, 2018 10:44 am
jay (support) offline
Site Admin
User avatar
Posts: 18199
Joined: Mar 19, 2008
Location: Austin, Texas

Re: Back to the Future - Storing historical data in SQL Logg

kw123 wrote:
back to the logger question.. I believe - and Matt/Jay correct me if I am wrong . indigo simply stores
in table device_history_deviceId: seq Id, currentTimestamp + value for each statename
When a state changes.


Correct. SQL Logger is just that - it logs changes when they happen. There is no other logic applied nor is there any mechanism to post process - we leave that up to the consumer of the data.

Jay (Indigo Support)
Twitter | Facebook | LinkedIn

Posted on
Fri Jan 05, 2018 12:17 pm
InsteonDiego offline
Posts: 48
Joined: Dec 07, 2016

Re: Back to the Future - Storing historical data in SQL Logg

Thanks Jay. I'll have a whack at storing the historical data in a log file and consuming as Karl suggests. Appreciate all the guidance!

Adam

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 5 guests

cron