I am new to Home Assistant, but quite experienced with OpenHAB. I have set up my first home assistant server, with the following characteristics:
Virtual Machine in VMWare, installed with the OVA file;
KNX bus integrated with currently 2 lights;
Recorder with MariaDB backend, on a Synology NAS, which I suppose works fine.
I am able to toggle ON or OFF the 2 lights with HA, I can see their history in the HA webpage, and I can see the entries in the “states” table with sql queries. However, when trying to find the events in the “events” table, I have nothing. All the events in the table have the event_type as “NULL”, and in the table event_types, there are only 20 entries, and the “state_changed” entry is not present.
I am wondering what I did wrong. I did not install the MariaDB container on the HA server itself, as I’m using MariaDB on Synology, where I gave the user “hass” the full permissions on the “HASS” database, but no server wide permissions.
What does your SQL query look like, can you post. With the recent releases the database in HA has gotten a bit more complex. The old days of a simple one table query for most useful info is no longer possible. Below is an example I shared on new vs. old queries, good hunting! :
I understand the queries you sent. On my side, I don’t have queries yet. I was going through the docs, and saw the state_changed event_type, but on my installation, I don’t even see it in the event_types table
Does this mean that the data model has changed ? That I cannot rely on the events table to build my queries and that I should do it with states table and other joined tables ?
Hmmmmm i think I find why. This page (Home Assistant Events | Home Assistant) explains it: “ All events except state_changed are stored in the events table”.
The only question remains: is it normal that all events in the events table have “NULL” as event_type ?
SELECT *
FROM “events” where event_type is not null
Apologies, I have not messed around with the events tables very much, I mostly query the states stuff. I think the column to look at is ‘event_type_id’ rather than ‘event_type’? ‘event_type’ may have been deprecated.
I’m not sure if this is the kind of data you are looking for or not, but here is a first pass at query to get all the events for ‘lights’ in the last day. This is a pretty rugged bit of SQL by me, I hope someone can show a cleaner bit of code. Also, I use Postgresql, rather than MariaDB, so your syntax may vary, good hunting! :
-- get light events for last day
SELECT
events.event_id,
events.data_id,
event_data.shared_data::jsonb ->> 'domain' AS "domain",
event_data.shared_data,
events.event_type_id,
event_types.event_type,
to_char(to_timestamp(events.time_fired_ts), 'YYYY-MM-DD HH24:MI:SS') AS time_fired_ts
FROM
events
LEFT JOIN event_data ON (events.data_id = event_data.data_id)
LEFT JOIN event_types ON (events.event_type_id = event_types.event_type_id)
WHERE (events.event_type_id = 12
AND event_data.shared_data::jsonb ->> 'domain' = 'light'
AND to_timestamp(events.time_fired_ts) > now() - interval '1 days')
ORDER BY
events.time_fired_ts DESC;
I’ll have a look at this query, but it seems that it won’t be easy to simplify it. In MariaDB, I was thinking of views to store the queries and then use the views for the charts.
As teskanoo proposed, replace the left join by inner join. It won’t take values not present in the other table, but it might be more performant.
