For example, I want to get the daily average solar production in the past 7 days.
#How do you query the data in the energy dashboard?
surreal crest
torn latch
The energy dashboard aggregates this data as opposed to using entities to read this as raw values
You can easily rebuild it with some extensive chart card like apex charts or do the query yourself with an sql sensor
surreal crest
How do I get the result as an entity that I can use in an automation?
torn whale
You can't (directly). At least not for a sensor that has state_class: total or state_class: total_increasing. The only thing you can get for these entities in an automation is the cumulative sum of all values up to the latest reading, which is most of the times not what you need.
This is because you're missing one important detail on such entity's 'value' when you try to read it in an automation: the date + time for which you need that value. The standard methods for retrieving a value (states(), state_attr(), etc) don't have a means to provide this information. This is why you will always get the total usage when you do try to get it from an automation.
Depending on what your needs are there are various alternative methods you can use, for example the 'utility meter' helper can be useful in some scenarios.
surreal crest
Can I use a helper integral sensor to integrate the solar production entity over 7 days?
rapid fossil
integrals don't reset or do any kind of rolling time period, they just increase forever
you can use a utility meter with an integral to add a weekly reset
torn whale
Sadly the bug in "Terminal & SSH" 's copy-paste still hasn't been solved (https://github.com/home-assistant/addons/issues/3608). This makes it very hard to do actual sql queries against the home assistant database, so I've kind of lost interest in doing any queries at the moment. It can be done, but it's definitely not an easy query to build.
torn latch
Sadly the bug in "Terminal & SSH" 's copy-paste still hasn't been solved (https:...
You don't need the terminal add all to make queries on the db tho?
If you need to use the results inside homeassistant you can use the SQL integration https://www.home-assistant.io/integrations/sql/
If you want to just peek around there is an SQL viewer addon or if you actually want to work on the db file (you shouldn't as this may break a lot if you don't watch out) you can simply access it through a network share and then open it with whatever tool you want on your pc. Alternatively to using a remote ssh client
For later it helps a lot to shut down ha core so there are no access issues with the file itself
torn whale
That "SQLite Web" add-on (at least I think that's the one you're referring to when you mention "SQL viewer", if not, please point me in the right direction) is not very useful for more complex queries. Querying LTS requires more complex queries than just 'peeking around', unless you're prepared to do a lot of the logic in some other layer, which is not possible if you intend to run the query from the sql integration. The sql integration is indeed used to retrieve the results of the completed query, but to write and test a query it's much more useful to use sqlite3 from the terminal window.
torn latch
Im not quite sure I can follow you on why the addon or the integration couldnt do more complex queries as opposed to the terminal, as both just execute the query you type down. Unless you are talking about more script like behaviour beyond executing multiple queries, which is mostly a limitation of the dialect. the alternative to use an actual terminal to connect via ssh is available aswell as mounting the db directly to your prefered ide.