1

I am working on a data logger for a home automation system. It periodically receives data from the HA controller when the state of something changes, in a key => value format. I'd like to dump this into a MySQL database.

When the HA controller sends this data, it doesn't include every property of an object. An example is shown below:

Initial return

stdClass Object
(
 [name] => Washing Machine
 [altid] => 10
 [id] => 31
 [category] => 3
 [subcategory] => 0
 [room] => 2
 [parent] => 1
 [status] => 1
 [light] => 30
 [kwh] => 30.2700
 [state] => -1
 [comment] =>
 [watts] => 108.5
)

Incremental update

stdClass Object
(
 [altid] => 10
 [id] => 31
 [subcategory] => 0
 [room] => 2
 [parent] => 1
 [status] => 1
 [light] => 30
 [kwh] => 30.2700
 [state] => -1
 [comment] =>
 [watts] => 96.7
)

As you can see, the later update doesn't include the name or category of the object. With other object classes, there could be other properties missing.

My question is what would be the best way to record this data, so that I can run a query to retrieve the state of every property at a single point in time? Some sort of fruity query to create a view possibly?

Thanks for the help

Stuart475898
  • 11
  • 1

2 Answers2

0

Since it's always giving you the ID (hopefully) you can look-up missing properties as events come in and populate your record with missing information before saving. You would need to select from previous events, or have a definition of devices already created in some master table of categories and names.

The other way is to not care about missing metrics and take care of that during the UI/graphing/reporting phase. You can build a UI that knows that ID 31 is the Washing machine and can subsequently graph all properties over time. The UI can continue charting any values that aren't present by looking at previous values or choosing to graph at 0.

Doing it this way, it would be tricky to build a single query that could identify any value at any point in time because it would have to keep scanning backwards in time until it found a value. But, again, in the graphing sense it really wouldn't matter that much because you would hopefully have a date range large enough to accommodate missing values.

You might also benefit from saving data into a time series database (like influx db) instead of a relational one.

chugadie
  • 2,786
  • 1
  • 24
  • 33
0

I found the solution eventually in another SO post: MySQL: Get Last Distinct Set of Records

The top answer by smdrager is what I have used. There appears to be no difference in speed between only a few rows in the table, and thousands.

Community
  • 1
  • 1
Stuart475898
  • 11
  • 1