It is pretty smart about finding the correct settings for you. RAWGraphs is a great online tool to quickly visualise data.Īfter uploading your recently created csv file, RAWGraphs gives you a lot of options for correctly parsing your data. * also grouping by only the date ensures only one row per day */ * using the date function we can strip the time from the created field */ ORDER BY ' id ' SELECT date(created), mean If, for example, you turn a light on from the dashboard, behind the scenes a call_service event is fired. The event_data uses a JSON field which contains all data the event contained. But most importantly it includes a foreign key data_id which references the event_data table.

The events table only includes metadata like the event type, when the event was triggered and a few more fields. This includes system things like the loading of a component or when an automation was triggered. events and event_data #Įvents are things that can happen in your Home Assistant installation. I won't explain every table, we'll focus on States, Events and Statistics. These are the tables Home Assistant uses, as of version 2022.6.4. In part, this comes from a good database architecture, but also from making use of JSON fields to store additional data, which would otherwise need many additional fields in the tables. Understanding the structure #Ĭompared to many other systems, Home Assistant doesn't have that many tables to provide all the features it does. home/pi/path-to-configuration-folder-of-home-assistant/home-assistant_v2.dbĬlick on 'Test' to make sure you've filled out everything correctly and click 'Connect' to drop into the database. Most likely this will be a path like this: This means that TablePlus will first connect to the machine with SSH before connecting to the database itself.ĭatabase path: Enter the absolute path to the database file which is used by Home Assistant. Over SSH: you will connect the database using an SSH tunnel. To connect to the database, create a connection that looks like the following: If you are on Windows a popular alternativ is HeidiSQL, which also supports SQLite. I'm using TablePlus for Mac to connect to the database. To follow the next steps you need to have SSH access to the machine running Home Assistant.

Tableplus export database