Salut,
j’ai exactement ça, car je voulais quelque chose de plus manipulable que InfluxDB que je n’aimais pas trop après plusieurs mois d’utilisation.
J’ai un serveur MariaDB sur lequel j’ai ma base de HA, mais aussi ma base à long terme.
Un évènement de la DB qui toutes les nuits récupère de HA les infos qui m’intéressent pour les coller dans ma table conso_maison, qui me permet de garder tout ça.
Depuis que j’ai fait ça avec HA (3 ans), j’ai eu 3 fois des corrections à faire à cause de l’évolution du modèle de données de HA, le changement majeur c’était en début 2023.
Ma base contient ces mêmes infos depuis presque 10ans car j’y ai déjà mis les données que j’avais récupéré de Domoticz et d’une autre système.
Le SQL est un peu bourrin mais ça marche. Il y a en complément une première requête qui créé l’entrée de chaque jour. Et celle-ci récupère les données:
UPDATE conso
SET
VOITURE = (SELECT max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "4185" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
CONSO_MAISON = (SELECT max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "65" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
CONSO_SOLAR = (SELECT max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "120" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
EXPORT = (SELECT max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "119" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
PROD_SOLAR = (SELECT (max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3)))) / 1000 FROM homeassistant.states
WHERE metadata_id = "200" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
HC = (SELECT max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "52" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
HC_INDEX = (SELECT max(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "52" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
HP = (SELECT max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "237" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
HP_INDEX = (SELECT max(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "237" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
demarrages = (SELECT max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "103" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
heures_appoint1 = (SELECT max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "3" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
heures_appoint2 = (SELECT max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "102" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
heures_comp = (SELECT max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "43" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
puissance_conso = (SELECT max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "29" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
puissance_chauffage = (SELECT max(CAST(state as decimal(10,3))) - min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "54" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
temp_ext = (SELECT avg(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "37" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
temp_ext_min = (SELECT min(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "37" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
temp_ext_max = (SELECT max(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "37" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
temp_depart = (SELECT avg(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "101" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
temp_consigne = (SELECT avg(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "9" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
temp_jean = (SELECT avg(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "243" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
temp_parents = (SELECT avg(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "90" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
temp_couloir = (SELECT avg(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "241" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY)),
temp_garage = (SELECT avg(CAST(state as decimal(10,3))) FROM homeassistant.states
WHERE metadata_id = "40" AND state <> "unknown" AND state <> "unavailable" AND state <> '' AND DATE(FROM_UNIXTIME(last_updated_ts + 7200)) = DATE(NOW() - INTERVAL 1 DAY))
WHERE date = date(NOW() - INTERVAL 1 DAY)
