Creating a Last 30 days energy sensor

Hi

I would like to create a bunch of sensors for my devices which would tell me eg how much energy they’ve used over 30 days, or much many times they’ve been turned on etc. my devices have the constantly accumulating energy consumption sensor.

The statistics platform can do this using the state_characteristic change but it is limited to the recorder data, so 10 days I my case and does not access the long-term statistics. I would like to have such sensors over different periods (eg year) and don’t really need the per-second data, so recording the entities for the whole period is a waste.

The long-term statistics contains everything I need. Indeed the energy dashboard basically does what I want in a dynamical fashion, but does not make the data accessible to further computation.

Utlity meters kind of do the opposite of what I want since they reset to zero and only are close to what I want at the end of the period. And the n I can’t even plot them long-term in a statistics card.

Is there any way of doing this? Some hacs integration? Seems like this is a natural starting point for any analysis of energy savings or some realistic analysis of the energy mix etc. maybe this is too data-sciencey for HA?

If you’re wanting long term statistics, I think you’re looking at something like influxDB.

Or if long-term statistics has what you want, you could just query the database directly as well.

1 Like

Thanks! I guess going the sql route would be a good way – but I’ve no idea of the syntax. Could someone point me at some simple docs or show me an example of how to do this so I can get started somewhere? To be concrete:

I have a total_increasing energy sensor with LTS: e.g. sensor.dishwasher_energy .
I would now like to create an sql sensor which would get the closest recorded value of this energy sensor 30 days ago. What’s the SQL query here?

OK, I’ve learned some things and poked blindly in others. I’ve managed to construct such a query – is this efficient?

SELECT state
FROM "statistics"
WHERE metadata_id = (SELECT id FROM "statistics_meta" WHERE statistic_id = 'sensor.electricity_total_energy') 
AND DATETIME(start) >= DATETIME("now","-30 days","-2 hours")
AND DATETIME(start) < DATETIME("now","-30 days","-1 hour")

and then I have a value template

{{states('sensor.electricity_total_energy')|float  - value|float}}

A couple of follow on questions:

  • How do I get this to evaluate once an hour – more than that is counterproductive, since the LTS is one per hour. All the SQL sensors will have same cadence, so if there is a global setting, that’s fine.
  • Is there some way of doing it through YAML? I want to make 20-30 of these.

I am fairly certain it will use the normal polling interval which I believe is 30 seconds. You could disable polling entirely if you wanted for that addon in the system options on that addon and then use an automation to trigger this.

  • service: homeassistant.update_entity