# Como criar uma base de dados leve no HA com histórico e apresentação gráfica
**URL:** https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888
**Category:** Tutoriais
**Created:** [24 Junho , 2020 02:58 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888 "2020-06-24T02:58:42Z")
**Posts on this page:** 20
**Page:** 10
### Author:  [@MarAE](https://forum.cpha.pt/u/MarAE)
#### Post date: [13 Março , 2021 12:49 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/186 "2021-03-13T12:49:47Z")
Sim, já tinha reparado nos teus posts anteriores.
Já agora, como estás a separar os sensores? Shellys EM, usando pinça em disjuntores especificos e separados, ou (outro _devices_) directamente ligados à L & N dos aparelhos?
Oh, talvez até apenas smart plugs - com registo de consumo?
A minha ideia era ver da placa vitrocerâmica e forno, mas com o consumo de um e outro excedendo os 2500W - só a placa pode chegar aos 6.500W…
Talvez acabe por configurar o quadro e aumente o nº de disjuntores. Espaço ainda há.
---
### Author:  [@bbaixo](https://forum.cpha.pt/u/bbaixo)
#### Post date: [13 Março , 2021 22:21 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/187 "2021-03-13T22:21:48Z")
Viva,
Consumo geral: Shelly 3EM
Consumo iluminação: Shelly 1PM e Shelly 2.5
Consumo maqs da cozinha: Shelly 1PM
Systemas IT: Shelly Smart Plug S
Para forno e placa a melhor opção é optar por um Shelly EM com duas pinças
---
### Author:  [@MarAE](https://forum.cpha.pt/u/MarAE)
#### Post date: [14 Março , 2021 00:12 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/188 "2021-03-14T00:12:19Z")
Realmente, os Shelly começam a ser como os Raspberries.
Nunca é demais ter mais uns quantos.
---
### Author:  [@MarAE](https://forum.cpha.pt/u/MarAE)
#### Post date: [16 Março , 2021 03:46 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/189 "2021-03-16T03:46:53Z")
Hmmm, devo estar a fazer algo mal:
```auto
automation:
- alias: "Bi-horário EDP-semanal"
initial_state: true
trigger:
- platform: homeassistant
event: start
action:
- service: shell_command.python_install_pymysql_dependency
- alias: "Registar o consumo diário bi-horário"
initial_state: true
trigger:
- platform: state
entity_id:
- sensor.daily_energy_shelly_peak_offpeak_offpeak
- sensor.daily_energy_shelly_peak_offpeak_peak
action:
- service: shell_command.daily_insert_mysql
data_template:
table: consumobi_energia
value: >
{{ trigger.to_state.state }}
column: >
{% if trigger.entity_id == "sensor.daily_energy_shelly_peak_offpeak_offpeak" %}
consumobi_energia_diario_offpeak
{% endif %}
{% if trigger.entity_id == "sensor.daily_energy_shelly_peak_offpeak_peak" %}
consumobi_energia_diario_peak
{% endif %}
sensor:
- platform: sql
db_url: mysql://XXX:XXX.@core-mariadb/custom_data
queries:
- name: consumobi_electricidade_offpeak
query: >-
SELECT
CONCAT(
'[',
GROUP_CONCAT(DAY(date) SEPARATOR ','),
']'
) AS labels,
CONCAT(
'[',
GROUP_CONCAT(consumobi_energia_diario_offpeak SEPARATOR ','),
']'
) AS data,
CONCAT(
'["',
GROUP_CONCAT(IF(`consumobi_energia_diario_offpeak` >= 10, 'red', IF(`consumobi_energia_diario_offpeak` >= 6, 'orange', 'green')) SEPARATOR '","'),
'"]'
) AS colors,
ROUND(SUM(consumobi_energia_diario_offpeak), 2) AS total,
FORMAT(round(((sum(consumo_energia_diario_offpeak) * 0.0923)*1.23), 2), 2) AS 'total_eur',
'info in attributes' AS value
FROM consumobi_energia
WHERE MONTH(date) = MONTH(CURRENT_DATE);
column: "value_offpeak"
- name: consumobi_electricidade_peak
query: >-
SELECT
CONCAT(
'[',
GROUP_CONCAT(DAY(date) SEPARATOR ','),
']'
) AS labels,
CONCAT(
'[',
GROUP_CONCAT(consumobi_energia_diario_peak SEPARATOR ','),
']'
) AS data,
CONCAT(
'["',
GROUP_CONCAT(IF(`consumobi_energia_diario_peak` >= 10, 'red', IF(`consumobi_energia_diario_peak` >= 6, 'orange', 'green')) SEPARATOR '","'),
'"]'
) AS colors,
ROUND(SUM(consumobi_energia_diario_peak), 2) AS total,
FORMAT(round(((sum(consumo_energia_diario_peak) * 0.1833)*1.23), 2), 2) AS 'total_eur',
'info in attributes' AS value
FROM consumobi_energia
WHERE MONTH(date) = MONTH(CURRENT_DATE);
column: "value_peak"
```
Como ainda só há registo do “offpeak”, dadas as horas, no HeidiSQL, experimentei o chartjs:
```auto
chart: bar
custom_options:
showLegend: false
data:
datasets:
- backgroundColor: '${states["sensor.consumobi_electricidade_offpeak"].attributes.colors}'
borderWidth: 1
data: '${states["sensor.consumobi_electricidade_offpeak"].attributes.data}'
label: Eletricidade
labels: '${states["sensor.consumobi_electricidade_offpeak"].attributes.labels}'
entity_row: false
options:
scales:
yAxes:
- ticks:
beginAtZero: true
title:
display: true
text: >
${"Consumo energético Mês Corrente -> " +
states["sensor.consumobi_electricidade_offpeak"].attributes.total_eur + "€"}
type: 'custom:chartjs-card'
```
Mas o gráfico aparece, no eixo X, desconfigurado, e sem valores apresentados.
No HeidiSQL, o offpeak está a 0,65.
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [16 Março , 2021 08:34 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/190 "2021-03-16T08:34:33Z")
Assim é complicado de perceber, mostra os dados obtidos pelo Sensor SQL, e um printscreen do gráfico.
---
### Author:  [@MarAE](https://forum.cpha.pt/u/MarAE)
#### Post date: [16 Março , 2021 10:19 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/191 "2021-03-16T10:19:06Z")
Base de dados:
Graph:
Mas o problema está, sem dúvida, nos sensores:
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [16 Março , 2021 17:29 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/192 "2021-03-16T17:29:50Z")
Verifica se a query funciona bem
---
### Author:  [@djm](https://forum.cpha.pt/u/djm)
#### Post date: [23 Março , 2021 06:16 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/193 "2021-03-23T06:16:23Z")
Tentei adaptar isto ao meu tarifário bihorário mas não consigo colocar a funcionar. Também não percebo muito bem o código.
Já consigo recolher bem os dados:
Mas só obtenho este resultado:
Partilho o meu base\_dados.yaml:
```auto
shell_command:
daily_insert_mysql: "python3 /config/py_scripts/daily_insert_mysql.py --host=core-mariadb --user=... --password=... --db=custom_data --table={{ table }} --value={{ value }} {{ '--col='+column if column is defined }}"
python_install_pymysql_dependency: "pip install pymysql"
automation:
- alias: "Instala a dependencia no arranque do ha"
initial_state: true
trigger:
- platform: homeassistant
event: start
action:
- service: shell_command.python_install_pymysql_dependency
- alias: "Registar o consumo diário de energia no vazio"
initial_state: true
trigger:
- platform: state
entity_id: sensor.daily_energy_vazio
action:
- service: shell_command.daily_insert_mysql
data_template:
table: consumo_energia
value: >
{{ trigger.to_state.state }}
column: >
{% if trigger.entity_id == "sensor.daily_energy_vazio" %}
consumo_energia_diario_vazio
{% endif %}
- alias: "Registar o consumo diário de energia fora do vazio"
initial_state: true
trigger:
- platform: state
entity_id: sensor.daily_energy_fora_de_vazio
action:
- service: shell_command.daily_insert_mysql
data_template:
table: consumo_energia
value: >
{{ trigger.to_state.state }}
column: >
{% if trigger.entity_id == "sensor.daily_energy_fora_de_vazio" %}
consumo_energia_diario_fora_do_vazio
{% endif %}
- alias: "Insere na tabela o número de Horas que a bomba de calor esta ligada"
initial_state: true
trigger:
- platform: state
entity_id: sensor.horas_bomba_calor
condition:
- condition: template
value_template: >
{{ (trigger.to_state.state | float) != 0.0 }}
action:
- service: shell_command.daily_insert_mysql
data_template:
table: horas_bomba_calor
value: >
{{ trigger.to_state.state }}
column: bombacalor
sensor:
- platform: history_stats
entity_id: automation.bomba_de_calor_desligada
state: 'on'
name: Horas Bomba Calor
start: '{{ now().replace(hour=0, minute=0, second=0) }}'
end: '{{ now() }}'
- platform: sql
db_url: mysql://...:...@core-mariadb/custom_data
queries:
- name: consumo_electricidade
query: >-
SELECT
CONCAT(
'[',
GROUP_CONCAT(DAY(date) SEPARATOR ','),
']'
) AS labels,
CONCAT(
'[',
GROUP_CONCAT(consumo_energia_diario SEPARATOR ','),
']'
) AS data,
CONCAT(
'["',
GROUP_CONCAT(IF(`consumo_energia_diario` >= 10, 'red', IF(`consumo_energia_diario` >= 5, 'orange', 'green')) SEPARATOR '","'),
'"]'
) AS colors,
ROUND(SUM(consumo_energia_diario), 2) AS total,
FORMAT(round(((sum(consumo_energia_diario) * 0.15184 + 0.1183 + 0.041 + 0.107)*1.23), 2), 2) AS 'total_eur',
'info in attributes' AS value
FROM consumo_energia
WHERE MONTH(date) = MONTH(CURRENT_DATE);
column: "value"
```
Também queria criar um gráfico em que visualizo o número de horas que a minha bomba de calor está ligada. Já consigo recolher os dados. O meu problema está em criar um gráfico com os dados:
Se me conseguirem ajudar, agradeço. A mim parece-me que me está a faltar conhecimentos na parte do código da criação de gráficos ou da consulta de dados na tabela. Se houver um bom tutorial que eu possa ler, agradeço também uma sugestão.
---
### Author:  [@bbaixo](https://forum.cpha.pt/u/bbaixo)
#### Post date: [23 Março , 2021 09:20 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/194 "2021-03-23T09:20:07Z")
Viva @djm ,
Um nota, que me salta à vista.
Os teus campos de registo em mariaDB possuem estes nomes:
E na tua query o nome não está correto:
Tens q fazer corresponder os nomes dos campos das tabelas no código da query.
Experimenta isto:
```auto
query: >-
SELECT
CONCAT(
'[',
GROUP_CONCAT(DAY(date) SEPARATOR ','),
']'
) AS labels,
CONCAT(
'[',
GROUP_CONCAT(consumo_energia_diario_vazio SEPARATOR ','),
']'
) AS data,
CONCAT(
'["',
GROUP_CONCAT(IF(`consumo_energia_diario_vazio` >= 10, 'red', IF(`consumo_energia_diario_vazio` >= 5, 'orange', 'green')) SEPARATOR '","'),
'"]'
) AS colors,
ROUND(SUM(consumo_energia_diario_vazio), 2) AS total,
FORMAT(round(((sum(consumo_energia_diario_vazio) * 0.15184 + 0.1183 + 0.041 + 0.107)*1.23), 2), 2) AS 'total_eur',
'info in attributes' AS value
FROM consumo_energia
WHERE MONTH(date) = MONTH(CURRENT_DATE);
column: "value"
```
---
### Author:  [@arjotema](https://forum.cpha.pt/u/arjotema)
#### Post date: [24 Março , 2021 22:15 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/195 "2021-03-24T22:15:07Z")
Olá, Boa noite.
Eu também tenho a mesma duvida sobre o gráfico da horas que algum dispositivo está ligado!
Já configurei a base de dados e funciona tudo bem quer no consumo elétrico, quer no tempo que a caldeira está ligada, mas o valor gravado é por exemplo 1.18, como se traduz isso em horas e minutos?
Aqui o package completo
```auto
### controle de consumos ###
shell_command:
daily_insert_mysql: "python3 /config/py_scripts/daily_insert_mysql.py --host=core-mariadb --user=user --password=password --db=consumos --table={{ table }} --value={{ value }} {{ '--col='+column if column is defined }}"
python_install_pymysql_dependency: "pip install pymysql"
#### Sensores #####
sensor:
- platform: history_stats
entity_id: switch.sonoff_caldeira
state: 'on'
name: horas Caldeira
start: '{{ now().replace(hour=0, minute=0, second=0) }}'
end: '{{ now() }}'
- platform: sql
db_url: mysql://user:password@core-mariadb/consumos
queries:
- name: consumo_electricidade
query: >-
SELECT
CONCAT(
'[',
GROUP_CONCAT(DAY(date) SEPARATOR ','),
']'
) AS labels,
CONCAT(
'[',
GROUP_CONCAT(consumo_energia_diario SEPARATOR ','),
']'
) AS data,
CONCAT(
'["',
GROUP_CONCAT(IF(`consumo_energia_diario` >= 10, 'red', IF(`consumo_energia_diario` >= 5, 'orange', 'green')) SEPARATOR '","'),
'"]'
) AS colors,
ROUND(SUM(consumo_energia_diario), 2) AS total,
FORMAT(round(((sum(consumo_energia_diario) * 0.15184 + 0.1183 + 0.041 + 0.107)*1.23), 2), 2) AS 'total_eur',
'info in attributes' AS value
FROM consumo_energia
WHERE MONTH(date) = MONTH(CURRENT_DATE);
column: "value"
- platform: sql
db_url: mysql://user:password@core-mariadb/consumos
queries:
- name: tempo_caldeira
query: >-
SELECT
CONCAT(
'[',
GROUP_CONCAT(DAY(date) SEPARATOR ','),
']'
) AS labels,
CONCAT(
'[',
GROUP_CONCAT(caldeira SEPARATOR ','),
']'
) AS data,
CONCAT(
'["',
GROUP_CONCAT(IF(`caldeira` >= 10, 'red', IF(`caldeira` >= 5, 'orange', 'green')) SEPARATOR '","'),
'"]'
) AS colors,
ROUND(SUM(caldeira), 2) AS total,
# FORMAT(round(((sum(caldeira) * 0.15184 + 0.1183 + 0.041 + 0.107)*1.23), 2), 2) AS 'total_eur',
'info in attributes' AS value
FROM horas_caldeira
WHERE MONTH(date) = MONTH(CURRENT_DATE);
column: "value"
##### automações #####
automation:
- alias: "Instala a dependencia no arranque do ha"
initial_state: true
trigger:
- platform: homeassistant
event: start
action:
- service: shell_command.python_install_pymysql_dependency
- alias: "Registar o consumo diário de energia"
initial_state: true
trigger:
- platform: state
entity_id: sensor.energy_today
action:
- service: shell_command.daily_insert_mysql
data_template:
table: consumo_energia
value: >
{{ trigger.to_state.state }}
column: >
{% if trigger.entity_id == "sensor.energy_today" %}
consumo_energia_diario
{% endif %}
- alias: "Insere na tabela o número de Horas que a caldeira esta ligada"
initial_state: true
trigger:
- platform: state
entity_id: sensor.horas_caldeira
condition:
- condition: template
value_template: >
{{ (trigger.to_state.state | float) != 0.0 }}
action:
- service: shell_command.daily_insert_mysql
data_template:
table: horas_caldeira
value: >
{{ trigger.to_state.state }}
column: caldeira
```
Obrigado por alguma dica
---
### Author:  [@75TiagoMatias](https://forum.cpha.pt/u/75TiagoMatias)
#### Post date: [27 Março , 2021 11:16 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/196 "2021-03-27T11:16:55Z")
Bom dia Rodolfo,
No Node-Red, depois de alterar os dados a amarelo, tenho este Debug:
É um erro e falhei alguma configuração ou é mesmo assim?
Porque no Card, no dropdown de “Ultimos 30 Dias” está impecável, mas se selecionar efetivamente o mês corrente, aparece:
Cheira-me que o problema ou está na configuração do Node-Red, ou na gravação dos dados para a mariaDB, apesar de me parecer que na base de dados está a gravar bem o valor:
Obrigado!!
Tiago Matias
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [27 Março , 2021 11:50 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/197 "2021-03-27T11:50:39Z")
Para ver o mês corrente, é por `Últimos 30 dias`, ao selecionar o mês corrente (Março), vai mostrar o mês de março do ano passado.
Isso foi implementado dessa forma porque se tiveres no inicio do mês, tipo dia 5, selecionar o mês corrente vai aparecer apenas 5 barras, para evitar isso, achei que fazia mais sentido ter o Últimos 30 dias, que mostra o gráfico mais preenchido
---
### Author:  [@j\_assuncao](https://forum.cpha.pt/u/j_assuncao)
#### Post date: [27 Março , 2021 12:22 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/198 "2021-03-27T12:22:44Z")
E não há maneira de mostrar sempre os últimos 12 meses? Assim ficava o gráfico sempre totalmente preenchido.
---
### Author:  [@75TiagoMatias](https://forum.cpha.pt/u/75TiagoMatias)
#### Post date: [27 Março , 2021 12:23 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/199 "2021-03-27T12:23:25Z")
Ahhh! Entendido!!
Então aquele Debug do Node-Red é mesmo assim, certo?
Obrigado!! 👌
---
### Author:  [@RodolfoVieira](https://forum.cpha.pt/u/RodolfoVieira)
#### Post date: [27 Março , 2021 13:53 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/200 "2021-03-27T13:53:02Z")
@75TiagoMatias relativamente ao “erro” no debug do nodered também me acontece, não sei se o @ricreis394 poderá dar algum input em relação a isso.
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [27 Março , 2021 14:09 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/201 "2021-03-27T14:09:43Z")
Sim , existe uma outra query que já foi publicada algures que mostra o gráfico dos últimos 12 meses
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [27 Março , 2021 15:23 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/202 "2021-03-27T15:23:11Z")
@75TiagoMatias Qual é o erro? O debug que mostras é só o resultado da query, que à primeira vista está correto
---
### Author:  [@RodolfoVieira](https://forum.cpha.pt/u/RodolfoVieira)
#### Post date: [27 Março , 2021 15:46 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/203 "2021-03-27T15:46:43Z")
@ricreis394 mas porque é que aparece isso no debug? não deveria aparecer nada.
---
### Author:  [@Marco\_Lima\_HA](https://forum.cpha.pt/u/Marco_Lima_HA)
#### Post date: [30 Março , 2021 07:34 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/204 "2021-03-30T07:34:13Z")
A minha pergunta é a seguinte:
[https://www.heidisql.com/](https://www.heidisql.com/)
Este programa tem que ser instalado num pc na rede que esteja sempre ligado correto?
Eu tenho o meu HÁ instalado num NAS…
Esta aplicação serve apenas para verificar se tudo está a correr bem se tens os valores a chegar ou precisas para mais alguma coisa.
Posso instalar tudo sem esta aplicação?
---
### Author:  [@RodolfoVieira](https://forum.cpha.pt/u/RodolfoVieira)
#### Post date: [30 Março , 2021 08:20 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888/205 "2021-03-30T08:20:50Z")
Olá, não o pc não tem que estar sempre ligado.
O heidisql é apenas um programa que neste caso serve para veres se a tabela está a ser criada.
Podes instalar tudo sim, mas seria bom teres algum programa, para veres se está a ser criada a base.
[Página anterior](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888.md?page=9)
[Próxima página](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888.md?page=11)