Logging Non Associated-HA data to a database (Plants)

domain_int · November 24, 2023, 9:20am

Hey Hey.

I have a small plastic hobby greenhouse in my backyard (its big for my backyard) that I grow veggies, herbs and leafy greens in. Generally I do these in batches at 1 month intervals. (no not those sort of herbacious veggies - REAL VEGETABLES AND KITCHEN HERBS)

My greenhouse lights and simple irrigation system is controlled by HA (zigbee plugs)

Id love to be able to log what Ive done to each plant/batch over time. I currently have an automation that casts my work/family calendar todo list and things like stock/crypto prices as well as todays weather on my 75" TV first thing in the morning and would love to cast some sort of “greenhouse status” as well telling me what was done and when and perhaps show what sort of cycle the plants are in.

I know this is not exactly the “use case” for HA but I was thinking that the diversity of what ppl are using it for, why shouldnt I see if I can do something cool with it for my gardening??

The question is: Whats the best way of logging non entity data with the ability to retrieve it thru HA (somehow) so that I can cast it AND would MariaDB the db of choice??

Essentially I want to do the following (take for instance a csv: header rows)

plant, batchno, germination date, pot no, plant date, plot no, fertilized date, water date, harvest date

tomato, t001, 29/02/2023, p001, , , , , ,
tomato, t001, , , 17/03/23, gh008, 17/03/23, 17/03/2023, ,
etc
etc

Essentially log data based on the batch no based on what I do in the greenhouse and then be able to recall it and show it on something like a dashboard (I havent thought this all the way thru yet)

Hope some kind soul may have a “rough idea” as to how I can get a seed and grow this idea

Thanks

tom_l · November 24, 2023, 9:55am

It is very much not the use case. Only long term statistics are kept for longer than 10 days by default, everything else is flushed.

There are plenty of gardening journal apps that would do this a whole lot better.

You might be able to connect home assistant to one of them to do things like send you reminders.

Biscuit · November 30, 2023, 2:20pm

Since HA is a computer and it processes and stores data, why not use it to store other information?

Yes - HA is a state machine with a short term memory, and the prime function is to respond to state changes, update the state model, and run automations, so anything else should avoid excessive use of CPU time and memory space.

That said, I believe that there could be an argument for augmenting the HA data with manually input information, and for long term data store for later use.

I am running a project to capture long term data from my solar PV system, specifically so that I can perform retrospective modelling on various energy tariffs and inverter control models.

I clearly do not want to mess up my HA, so I first experimented with my stand alone Raspberry Pi running Node-RED, and installed SQLite, capturing data from my HA. It worked, but I have moved to using MariaDB. To be ultra safe I purchased a new Odroid HA just to run Node-RED with MariaDB. My main HA runs a Node-RED flow each hour to push data to context, and once a day the full array goes to a sensor attribute which my second HA picks up and pushes to the database as hourly records.

I use MariaDB as an addon, and this is alongside and not in replacement of SQLite which HA uses. I store data as serialised JSON strings, so it is easy to change schema and repair existing data. It was easy to set up, is quicker than SQLite on my Pi, and seems compact. Also as I have installed phpMyAdmin, MariaDB can be accessed from the sidebar, and as it is a server SQL engine, I can access it from my desk PC. It works easily with Node-RED.

I capture solar performance, load and battery power status, as well as inverter settings, solar forecast, and weather forecast. I also capture my energy tariff and meter readings and other agile tariff half hourly figures.

I now have almost 180 days worth of data so far (less than 6 MiB storage) and plan to cross-analyse things like weather forecast with solar forecast to actual PV. Nothing quite like hindsight. Then tariff modelling, and inverter control testing. Maybe heuristic modelling control too?

At the moment I only collect HA entity values, but there is no reason why I could not add notes or manually collected data, such as when taking part in energy saving sessions, or if I have used the oven or done laundry. Indeed, our laundry activity, being an electric washing machine and drier, is prompted by sunny weather. So why not capture this ‘activity / action / intent’ manually in the same long term database?

Getting stuff back out of my private database and into HA with Node-RED and JSONata is easy, and I probably could also use the HA SQL integration. I only use Node-RED to both write and read, but I found a custom integration for writing using an HA notification (I have not tested this).

https://community.home-assistant.io/t/mysql-command/539839

My route was to install the MariaDB addon, and the phpMyAdmin, which I used to create a database and table, and then I used Node-RED MySQL node to write and read records. I can edit the database directly using phpMyAdmin from HA, so manual updates could be as simple as just using this tool.

Clearly not the ‘use case’ but then why not?

tom_l · November 30, 2023, 3:26pm

Because there are better tools for the job that take a whole lot less effort to implement.

domain_int · December 20, 2023, 11:15am

Thanks Geoff for your positive reply.

I will delve deeper into how you have achieved this as it seems to parallel what I need to do.

And Why Not? (rather than being short sighted as to a “5 years down the road possible use case”)

As a progression (which HA has always been progressive in its use case) I may also want to log the amount of light hours and watering time/volume that HA can supply under the hood as well as energy consumption as a byproduct per plot - it makes perfect sense to use HA to log this sort of data so that it is possible to create some sort of “status” from the data as it clearly seems to have the capability of doing so when I am ready to do so.

Also makes it easy to cast it to a TV every morning when I wake up.

Its called - the unknown (un)knowns (sometimes called unknown foresight)

I may reach out to you further down the line if I have any issues.

Many Thanks