The energy dashboard has some great graphs and panels. I configured the cost of my electricity and get a great read out of how much I’ve spent today/this week/last year on electricity for each appliance.
This can be added to a dashboard via one of the energy cards, but I don’t see anywhere where they’re available as sensors.
Can this data be accessed? Or incorporated into a template sensor in an easy way? I’m looking to use this data in automations and pull it via API.
Two options: you address this via SQL (this is in the statistics table) or you add your own sensor, e.g. utility meter(s)
Utility Meter - Home Assistant (home-assistant.io)
SQL - Home Assistant (home-assistant.io)
Thanks for the links, it would be great if this data was accessible via a sensor, but there is a workaround at least.
I just wanted to add some info on this because although these forums are great I really struggled to get enough info and I’m sure I am not alone. HA is quite difficult and very tricky to get right. So I’ll go through the steps I have taken as a newbie, some of these will be obvious to some but perhaps not to others.
First I will assume that people looking for this data have already implemented the energy tab and got values showing there.
I wanted to get the daily costs, i.e. the right hand values from the energy tab and use them elsewhere on a separate dashboard, rather than having to click on the energy tab.
Anyway here’s the data I wanted to grab …
So I started by installing SqlLite into HA and then in the query tab of the table statistics_meta I ran this query, to look for the data that corresponds to the name “Study”.
SELECT * FROM “statistics_meta” where statistic_id like ‘%study%’
The results give me this…
id statistic_id source unit_of_measurement has_mean has_sum name
6 sensor.study_hub_today_energy_cost recorder EUR 0 1 NULL
39 sensor.study_hub_current_power recorder W 1 0
So then I used the ID (6) in the next query on the statistics table:
SELECT round(
(SELECT round(max(sum), 2)
FROM “statistics”
WHERE metadata_id = 6
AND date(created_ts, ‘unixepoch’) = date(‘now’))
-
(SELECT round(min(sum), 2)
FROM “statistics”
WHERE metadata_id = 6
AND date(created_ts, ‘unixepoch’) = date(‘now’)),
2) AS sum
Although I know SQL well enough I realise others do not, so what the query does it to use the ID of the sensor to select the latest row from the table for today’s date, and then selects the earliest row from the table for today’s date, and then subtracts one from the other. This is because the statistics table, as far as I make out, stores the costs in accumulated amounts. So we take the latest figure, subtract the earlier figure, and the difference is the actual cost so far today. This should match the value on the energy tab.
Now to get this into a usable value I tried to use “configuration by YAML” section on SQL - Home Assistant but I had no joy at all, I spent days tweaking it and it never worked for me, so instead I used the sql integration option which is the first method shown on the page. Much simpler… I added the integration SQL and populated as follows:
I give the query a name, add “sum” to the column, paste in the query from above and click submit and finish. On the integration page when I click on the “study cost” integration it shows the following under the sensor tab, so now I have the actual cost in HA.
And finally I used a markdown card on my dashboard as follows……
I’m sure there are better and easier ways to get data into a statistics sensor, but this method works for me. I might try other methods as I get more familiar with HA, such as the proper statistics integration as per Statistics - Home Assistant.
Many thanks for this contribution Richard. Could you please verify exact syntax of the “select query” that goes into the SQL integration?
I am getting “SQL Query invalid” when entering the following (your query as listed above):
SELECT round(
(SELECT round(max(sum), 2)
FROM “statistics”
WHERE metadata_id = 6
AND date(created_ts, ‘unixepoch’) = date(‘now’))
-
(SELECT round(min(sum), 2)
FROM “statistics”
WHERE metadata_id = 6
AND date(created_ts, ‘unixepoch’) = date(‘now’)),
2) AS sum
you have to retype all the single quotes and double quotes, because copying them from the forum will result in errors.
Also, I changed the code a bit to take the last value of yesterday instead of the first value of today, as the starting point for the calculation. This seems to correspond exactly to the number in the energy dashboard:
SELECT round( (SELECT round(max(sum), 2) FROM statistics WHERE metadata_id = 317 AND date(created_ts, 'unixepoch') = date('now')) - (SELECT round(max(sum), 2) FROM statistics WHERE metadata_id = 317 AND date(created_ts, 'unixepoch') = date('now', '-1 days')), 2) AS sum
