Downloading data easily

I can see my data graphed in the Overview, but I would like to download the data for analysis outside of HA. Is there an easy way to grab all of the data from a given entity? By “easy” I mean that I don’t have to switch databases or learn how to create appropriate SQL statements.

By your definition of “easy”, there is no way to do what you want.

Thanks. It is a shame. It is an obvious need/request: a button on the graph that says “data in CSV form”

3 Likes

Is there a cookbook set of instructions somewhere to do what I want/need?

Probably not. The underlying database has changed a lot in the last year. You’ll just have to familiarize yourself with SQL queries. And it’ll be hard if you don’t have a db manager like phpmyadmin w/ mariadb. YOu’ll basically have to rely on searching the forums for recent posts doing the same thing.

My advice: don’t be afraid of simple SQL queries. The complication comes in when you want to do sophisticated transforms and combinations of multiple tables. You won’t need any of that for what you’re doing. Docs here plus a quick SQL tutorial should get you what you need. History - Home Assistant

But, you could also look at HA’s REST API, if that’s easier for you. REST API | Home Assistant Developer Docs

1 Like

Depending on what you want to do, there is another option. That would be to store the data in flat files instead of the database.

But…You’d have to define in advance which data you want to keep, it’s a bit of work to set it up, and you’re limited to appending one line at a time to plain ol’ text files.

One possible advantage is you don’t have the purge of old data. On the other hand, that means you have to manually manage the size of the file(s).

If I haven’t convinced you yet that learning SQL is easier, then look at the file integration. There’s a pretty decent example there. Look at this thread or this thread, where I posted some of how I did this. There are other examples on this forum, if you spend some time with a decent search engine.

CaptTom: thanks for your help. All that you did was make my head spin in a different direction.

I have used SQL for many years, but at an elementary level. What I seek is something that many people might desire: to export existing history data into a CSV file. Surely someone can create a simple SQL query that results in a CSV file containing all of the historical data for a given entity.

This reminds me of quite a few other programming situations that I encountered during my career, where you can do the impossible in a matter of minutes, but doing something simple can take weeks.

I want to calculate total energy use for each day and correlate it with outdoor temperature. I was going to integrate the power data using a spreadsheet. Again, this is such an obvious use of the system that it is astonishing that it is so complex.

I will stop complaining.

Jon

2 Likes

Make a feature request. This is open source, if it hasn’t been added, no one has wanted to add it or seen a need for it. Many people who want to do data analysis send their entities to graphina (sp?). So that might be another reason this hasn’t been added, because something else can do it better.

1 Like

I implemented this as part of my history explorer card as an experimental feature a while ago, but it didn’t make it in the release yet. I’ve uploaded a test version of the card on the github. You’ll have to install the latest build manually to test the feature, the HACS version doesn’t (yet) include it.

Press the export button to export all visible graphs and timelines to a CSV file over the currently requested time frame (up to 1 month).

image

Results look like this:

5 Likes

It’s actually a lot simpler than I probably made it seem.

You can get total energy use for each day using the history_stats integration. There are tons of examples, no doubt you’ll find one which works for you. Or ask here, lots of helpful folks.

Outdoor temperature is even easier; lots of different sources for that. Maybe your smart thermostats give you that, or your nation’s weather service, or a dedicated temperature sensor at your home.

You could even output both values, along with the date, to each line of a text file (csv format if you want.)

But… To correlate with daily energy use I’d use heating or cooling degree days, not any instantaneous temperature value. You can probably find a web site you can scrape those off, or maybe even download directly in csv format from a meteorological bureau. Do that outside of HA, and pull it in along with your text file from HA. I’ve actually done that with my daily fuel usage. It’s not real-time, but it’s not something I really need to look at in real time anyway.

That seems like the solution to me

HeyImAlex:

Perfect! How do I vote for it?!?

Jon

Nothing to vote for. Some testing and feedback on the beta would be appreciated. I’ll add the feature to the next release anyway, it’s been on the backlog for a while.

1 Like

How often are new versions of Home Assistant released?

Every first Wednesday of the month. But this has no bearing on the card mentioned by Alex.

Ah. Maybe I mis-understood “next release”

Next release of my custom card :slight_smile:

A little busy with another major release on my real life job right now, but I plan on pushing it onto HACS maybe later tonight or tomorrow. Just fine tuning the UI a bit more. In the mean time, the feature is already available. You just have to manually install the current build. It’s just copying a file basically.

1 Like

Release V1.0.18 of the history explorer card is now available on HACS and includes the new CSV export option (amongst other new stuff):

image

2 Likes

I downloaded and installed HACS. I downloaded the History Explorer Card in HACS, and it is visible there. But it is not visible as Card I can add into the Overview. What am I missing here?