Looking to add & write to a new database table from within HA

[andyb23 (Andy Bell)]

Hi there! I would like to add a table to my existing HA database, which is SQLite. My HA is installed on a RPI4 with attached SSD. The OS is setup and installed via the RPI imaging program, and use of HA has been so easy for my automations.

The table would consist of the following columns:
month (number)
timeslot (number)
timeslotCount (number)
usage (number)

This table would contain 576 rows max and would update 1 record every 30 minutes. I would like to perform this update via a HA script called by an automation, if that is possible. I have not done anything SQL-related with my HA other than using the SQLite Web addon to browse data (trying to understand the model).

I’m very familiar with SQLite from my day job, and comfortable with the HA scripts and automations I’ve built so far, but wondering if this idea is doomed or dangerous (I understand it’s dangerous to fiddle with the HA database, and I would rather not fiddle with Recorder), and so I’m thinking it’s better to add a second database purely for my use that could be reached from scripts I write - this would be my preferred option.

Clearly I am trying to avoid having to create 576 input_number entities if I can help it!

So my question - Is this do-able or not? Am I being naive about my goal here? If it is possible, and you know how to do this, please could you give me some pointers, such as:

• can SQL Web be used to query a second db in the config (or other) folder?
• how do scripts write to a different database?
• complications & things that have clearly not occurred to me!

Thanks
Andy

[tom_l]

Why do you want to do this?

[andyb23 (Andy Bell)]

Hello Tom

I did say, it was to avoid creating 576 helpers. My intention is to maintain an average of my half-hourly power usage.

I have a local-tuya power meter attached to my consumer unit and the HA sensor reports the current power usage since midnight.

Prior to having a string inverter and solar panels, my smart-meter would send half-hourly usage to the DCC where all smart meter data goes, and this data would have been available to me via my energy supplier. From that I was able to build up monthly averages of each half-hour. But since having PV installed, I only draw from the grid at certain times, and so this data is useless.

I hope that explains the goal. Do you have any info on my requirement?

[tom_l]

@andyb23

My intention is to maintain an average of my half-hourly power usage.

Why do you need 576 helpers for that?

Do you have 576 power sensors?

[andyb23 (Andy Bell)]

one for each half-hour in a day, and 12 of these. That’s 576.

The goal - to have a monthly average of each half-hour power usage

No I don’t have 576 power sensors. I have one. But I can use logic to generate 576 distinct records

[petro]

You’re definitely trying to fit a round peg into a square hole here. HA is not meant for database management and you’re actively circumventing the entire statistics & history engine. Is there a reason you’re not using the energy tab and it’s built in abilities, which already calculates these values on an hourly basis? You can even run comparisons to previous months/weeks/days/years.

[PeteRage]

I can see how that would be helpful. Pre summarizing data in a reporting table is pretty standard industry practice.

I would not add a table to the HA database, rather create a new database.

While 576 helpers sounds like a a lot, you could easily create a program or shell script to write the YAML into a package.

[tom_l]

Or, use one entity to calculate the 1/2 hour average and add it to a total. Then at the end of the month divide the total by 576 to get the average of the half hours for the month. That’s three entities.

[andyb23 (Andy Bell)]

No - each timeslot in each month will be an average of the power usage for that specific timeslot in that month only.

[andyb23 (Andy Bell)]

Thanks Pete for the positive response. So do you know if the SQL Web add-on can reach databases other than the standard HA db?

What is the best way to add/update records to a different db?

[petro]

I’m very confused why you aren’t using the built in energy tab that already does this exact comparison.

[andyb23 (Andy Bell)]

I was hoping not to have to go into why I didn’t use the Energy dashboard - it is related to the behaviour of the sensor linked to my power meter - it has spikes every so often (electrical-related) which skew the readings by a factor of 1000, and due to the fact that my helper could not be changed when this is detected, I had to create a input-template and manual midnight resetter. The template entity is not compatible with the device selector in the energy dashboard.

There is a reason I want the data in the structure I mentioned. It is to feed into an external model.

[petro]

@andyb23

I was hoping not to have to go into why I didn’t use the Energy dashboard - it is related to the behaviour of the sensor linked to my power meter - it has spikes every so often (electrical-related) which skew the readings by a factor of 1000, and due to the fact that my helper could not be changed when this is detected, I had to create a input-template and manual midnight resetter. The template entity is not compatible with the device selector in the energy dashboard.

You can likely fix that at the source. I suggest providing more information so we can help you detect these. The template integration in combination with filter integration can likely remove all false results.

@andyb23

It is to feed into an external model.

A simple single SQL sensor can get you this value with an automation. You don’t need to provide all the data at once. Just make a sensor that runs the calculation every specified half hour and send the data at the time of the calculation. I.e. 1 calculation that always runs, and you send 48 times throughout the day… if that makes sense. Granted, you may need 12 of these, still unclear what the “12 of these” means.

[andyb23 (Andy Bell)]

I reported the issue here some time ago.

Daily resetting Helper has jumped to value of its source

Hello. I have a daily resetting helper defined in my config file which uses a power meter sensor for its source. It always resets on a 00:05 cron. Today for some reason the source value dropped from its value of around 2.5 to 0 at 7:48 and upon the next update from the sensor, it jumped to the value of the source, as though it had started from scratch as a new helper I have attempted to change it to the correct value using the States page in Developer Tools but it just reverts back to countin…

Got zero responses

@petro

A simple single SQL sensor can get you this value with an automation. You don’t need to provide all the data at once. Just make a sensor that runs the calculation every specified half hour and send the data at the time of the calculation.

Tell me more, please!

[petro]

@andyb23

Got zero responses

That problem is very common, you likely would have gotten hits if you put it against the energy tag. Is this a template entity? If yes, post the template you’re using.

[petro]

@andyb23

Tell me more, please!

SQL

You would get the history of your current sensor through SQL, average the value over the last month (Will require templating). That sensor will update normally. You write a second automation that fires on the half hour and pulls the current value and sends it whereever you want to send it. No need to muck with adding items to a database.

[andyb23 (Andy Bell)]

utility_meter:

  daily_power_meter_custom_helper:
    unique_id: "daily_power_meter_custom_helper"
    source: sensor.power_meter_reading
    name: "Daily Power Meter Import Custom Helper"
    cron: "5 0 * * *"

this is what happens:

[petro]

What’s providing sensor.power_meter_reading, post that config

[andyb23 (Andy Bell)]

      power_meter_reading:
        unique_id: "power_meter_reading"
        friendly_name: "Power Meter Reading"
        unit_of_measurement: "kWh"
        value_template: "{{ (states('sensor.reserve_energy_circuit_1')|float(0)) / 100 }}"
        icon_template: mdi:flash

sensor.reserve_energy_circuit_1 is the local tuya sensor

[petro]

Yep, there’s your problem. You’re using float(0) which will set the value to 0 when sensor.reserve_energy_circuit_1 is unavailable.

This will make the sensor go unavailable when the source is unavailable, which will correct everything down the line.

      power_meter_reading:
        unique_id: "power_meter_reading"
        friendly_name: "Power Meter Reading"
        unit_of_measurement: "kWh"
        value_template: "{{ (states('sensor.reserve_energy_circuit_1')|float) / 100 }}"
        icon_template: mdi:flash
        availability_template: "{{ 'sensor.reserve_energy_circuit_1' | has_value }}"

EDIT: FYI this isn’t power even though you list it as power, it’s energy. Power would just be watts or kW as the unit.