Como criar uma base de dados leve no HA com histórico e apresentação gráfica

Tutoriais
#21

Não estou a conseguir apresentar mais que 1 sensor no mesmo gráfico. Nem sei se estou a perceber a estrutura.
Pretendo apresentar os dados da tabela consumo_dia no mesmo gráfico é possível?
image

sensor:
  - 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(energia_dia_aquecedorsuite SEPARATOR ','),
                  ']',
                  '[',
                  GROUP_CONCAT(energia_dia_sala SEPARATOR ','),
                  ']'
              ) AS data,
              CONCAT(
                  '["',
                  GROUP_CONCAT(IF(`energia_dia_sala` >= 10, 'red', IF(`energia_dia_sala` >= 5, 'orange', 'green')) SEPARATOR '","'),
                  '"]'
              ) AS colors,
              CONCAT(
                  '["',
                  GROUP_CONCAT(IF(`energia_dia_aquecedorsuite` >= 10, 'red', IF(`energia_dia_aquecedorsuite` >= 5, 'orange', 'red')) SEPARATOR '","'),
                  '"]'
              ) AS colors,
              ROUND(SUM(energia_dia_sala), 2) AS total,
              FORMAT(round(((sum(energia_dia_sala) * 0.1281)*1.23), 2), 2) AS 'total_eur',
              'info in attributes' AS value
          FROM consumo_dia
          WHERE MONTH(date) = MONTH(CURRENT_DATE);
        column: "value"
#22

Como queres ter vários valores no mesmo dia, penso que a melhor solução seja criar vários datasets, aqui podes ver um exemplo: https://www.chartjs.org/samples/latest/charts/bar/vertical.html
Mais logo vejo melhor como podes montar o código

#23

Estou a tentar seguir este tutorial, mas na parte do HeidiSQL aparece-me esta mensagem:
https://gyazo.com/bb9c0822c3dac0dcd66e254cc751bfe4

E tenho o addon ja configurado no na imagem acima.
O que poderá ser?

#24

No Addon abre a porta 3306

2 Curtiram
#25

Screenshot_1

Será isto que queres?

Este é o código utilizado para este gráfico, está com valores estaticos, para ser mais vantajoso é ires buscar os dados à Bae de dados
O mais importante dos dados que precisas é o data e o labels, a query tem de devolver um Array tipo isto: [12, 14, 11] por aí adiante consoante a quantidade de valores queiras, no caso do labels, devolve um Array mas com Strings em vez de Integers ou Floats, Tipo isto: ["Segunda", "Terça", "Quarta", "Quinta"]

Ou seja, os hífens em yaml podem significar 2 coisas, ou um array ou um objeto, mas para este caso, acho que o Array é o mais importante
Exemplo de Array:

- Segunda
- Terça
- Quarta
- Quinta

Que é a mesma coisa que: ["Segunda", "Terça", "Quarta", "Quinta"]

chart: bar
custom_options:
  showLegend: false
data:
  datasets:
    - backgroundColor: Blue
      borderWidth: 1
      data:
        - 10
        - 11
        - 10
        - 9
        - 9.6
        - 9
        - 11
      label: Lavar roupa
    - backgroundColor: Yellow
      borderWidth: 1
      data:
        - 2
        - 3
        - 5
        - 2
        - 3
        - 3
        - 5
      label: Frigorífico
    - backgroundColor: Orange
      borderWidth: 1
      data:
        - 5
        - 6
        - 5
        - 7
        - 3
        - 4
        - 5
      label: Placa
  labels:
    - Segunda
    - Terça
    - Quarta
    - Quinta
    - Sexta
    - Sábado
    - Domingo
options:
  scales:
    yAxes:
      - scaleLabel:
          display: true
          fontStyle: initial
          labelString: kilo Watt hour
        ticks:
          beginAtZero: true
  title:
    display: true
    text: Consumo por semana
type: 'custom:chartjs-card'
#26

Obrigado @ricreis394 com base no que apresentaste, estruturei desta forma:

chart: bar
custom_options:
  showLegend: false
data:
  datasets:
    - backgroundColor: '${states["sensor.consumo_electricidade"].attributes.colors}'
      borderWidth: 1
      data: '${states["sensor.consumo_electricidade"].attributes.data}'
      label: Sala
    - backgroundColor: '${states["sensor.consumo_frigorifico"].attributes.colors}'
      borderWidth: 1
      data: '${states["sensor.consumo_frigorifico"].attributes.data}'
      label: Frigorifico
    - backgroundColor: '${states["sensor.consumo_desumidificador"].attributes.colors}'
      borderWidth: 1
      data: '${states["sensor.consumo_desumidificador"].attributes.data}'
      label: Desumidificador
    - backgroundColor: '${states["sensor.consumo_cilindro"].attributes.colors}'
      borderWidth: 1
      data: '${states["sensor.consumo_cilindro"].attributes.data}'
      label: Cilindro
  labels: '${states["sensor.consumo_electricidade"].attributes.labels}'
entity_row: false
options:
  scales:
    yAxes:
      - ticks:
          beginAtZero: true
  title:
    display: true
    text: >
      ${"Consumo energético Frigorifico Mês Corrente -> " +
      states["sensor.consumo_frigorifico"].attributes.total_eur + "€"}
type: 'custom:chartjs-card'

Tenho uma duvida, é possivel no title do chart fazer a soma do valor de cada sensor ?
Tentei desta forma

      ${"Consumo energético Frigorifico Mês Corrente -> " +
      states["sensor.consumo_frigorifico"].attributes.total_eur  +
      states["sensor.consumo_electricidade"].attributes.total_eur + "€"}

MAs desta forma apresenta um valor muito alto

#27

Deverá funcionar fazer soma. Possivelmente o resultado que te aparece deve ser a soma dos dois como 2 palavras, e não a soma por matemática. Para que a soma seja feita matematicamente deves forçar a passar o valor para Float, experimenta com o código abaixo

${ "Consumo energético Frigorifico Mês Corrente -> " + (parseFloat(states["sensor.consumo_frigorifico"].attributes.total_eur) + parseFloat(states["sensor.consumo_electricidade"].attributes.total_eur)) + "€"}

#28

Boas pessoal necessitava de gravar dados na BD e usei este script mas tinha a necessidade de enviar para la a data pois quero gravar sempre ao dia 1 de cada mes, alterei o script para permitir enviar a data se alguem quiser

#!/usr/local/bin/python
# coding: utf8
import datetime
import argparse
## on homeassistant container:
## pip install pymysql
import pymysql
import warnings

def main(args):
  with warnings.catch_warnings():
    warnings.simplefilter("ignore")

    dt = datetime.datetime.today()
    if args.date is not None:
        dt = args.date;
    
    colname = (args.col if args.col is not None else "value")

    connection = pymysql.connect(
      host=args.host,
      user=args.user,
      password=args.password,
      db=args.db,
      charset="utf8mb4",
      cursorclass=pymysql.cursors.DictCursor
    )

    try:
      with connection.cursor() as cursor:
        sql = """CREATE TABLE IF NOT EXISTS `{table}` (
          `date` DATE NOT NULL,
          PRIMARY KEY (`date`))
          COLLATE='utf8mb4_unicode_ci'
          ENGINE=InnoDB;""".format(table=args.table)
        cursor.execute(sql)
        
        sql = "ALTER TABLE `{table}` ADD COLUMN IF NOT EXISTS `{col}` FLOAT NULL DEFAULT NULL;".format(table=args.table, col=colname)
        cursor.execute(sql)

        sql = "INSERT INTO `{table}` (`date`, `{col}`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE {col}=%s;".format(table=args.table, col=colname)
        sql_data = (dt.strftime("%Y-%m-%d"), args.value, args.value)
        cursor.execute(sql, sql_data)
      
      connection.commit()

    finally:
      connection.close()

    

if __name__ == "__main__":
  parser = argparse.ArgumentParser(description="Insert kWh value of current day to mariadb database. This creates table if not exists.")
  parser.add_argument('--host', type=str, required=True, help='REQUIRED: DB Host')
  parser.add_argument('--user', type=str, required=True, help='REQUIRED: DB User')
  parser.add_argument('--password', type=str, required=True, help='REQUIRED: DB Password')
  parser.add_argument('--db', type=str, required=True, help='REQUIRED: DB Name')
  parser.add_argument('--table', type=str, required=True, help='REQUIRED: DB Table')
  parser.add_argument('--value', type=float, required=True, help='REQUIRED: Value to insert')
  parser.add_argument('--col', type=str, required=False, help='OPTIONAL: Column name. Defaults to `value`')
  parser.add_argument('--date', type=datetime.date.fromisoformat, required=False, help='OPTIONAL: Date to Insert - format YYYY-MM-DD')

  args = parser.parse_args()
  
  try:
    main(args)
  except IndexError:
    raise ValueError("Argument required")

o parâmetro é --date e se nao for preenchido fica a data de hoje

#29

Obrigado pela colaboração,
Qual o caso de uso para esta implementação?

#30

Estou com um problema: apresentar em grafico o sensor do tempo de luz led strip ligado…
o 1º problema é que o sensor nao me esta a apresentar os dados como devia ou seja:
o sensor sensor.horas_luzes nos atributos so tem: friendly_name: horas_luzes
enquanto que o sensor sensor.consumo_electricidade esta ok pois apresenta no atributos:
labels: [3,4,5]
data: [1115.32,1136.56,1136.68]
colors: [“red”,“red”,“red”]
total: 3388.56
total_eur: 633.19
value: info in attributes
friendly_name: consumo_electricidade

este é o meu código:


sensor:
  - platform: history_stats
    entity_id: light.led_strip
    state: 'on'
    name: Horas Luz Led Strip
    start: '{{ now().replace(hour=0, minute=0, second=0) }}'
    end: '{{ now() }}'

# Aqui acima :point_up: está o sensor “manual” que criamos que neste caso se chama horas luz cozinha lembras-te mais acima onde te disse
# que ias ver o nome do sensor que deste no trigger aqui está é este. E tens também aqui definido no sensor o light.XXXX que é a tua luz 
#da cozinha , atenção que pode a tua luz ser light ou swich.


  - platform: sql
    db_url: mysql://xxx/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"

# faz uma queri na elecricidade para mostrar os dados: agrupa na base de dados consumo_electricidade , pela data (dia) , consumo_energia_diario
#é onde vai buscar os dados na tabela , e se o consumo energia diario for maior que 10 altera as cores, 5 , verde


  - platform: sql
    db_url: mysql://xxxx@core-mariadb/custom_data
    queries:
      - name: horas_luzes
        query: >-
          SELECT
              CONCAT(
                  '[',
                  GROUP_CONCAT(DAY(date) SEPARATOR ','),
                  ']'
              ) AS labels,
              CONCAT(
                  '[',
                  GROUP_CONCAT(luzledstrip SEPARATOR ','),    
                  ']'
              ) AS data,
              CONCAT(
                  '["',
                  GROUP_CONCAT(IF(`luzledstrip` >= 3, 'red', IF(`consumo_energia_diario` >= 2, 'orange', 'green')) SEPARATOR '","'),
                  '"]'
              ) AS colors,
              ROUND(SUM(luzledstrip), 2) AS total,
              FORMAT(round(((sum(luzledstrip) * 0.15184 + 0.1183 + 0.041 + 0.107)*1.23), 2), 2) AS 'total_horas_led',
              'info in attributes' AS value
          FROM horas_luzes
          WHERE MONTH(date) = MONTH(CURRENT_DATE);
        column: "value"

o que esta mal ?
já agora onde posso testar o código que esta na plataforma sql ?

#31

Basicamente tenho umas tarefas que posso concluir a qualquer altura do mes, e posso editar também a qualquer altura do mês mas para o gráfico só necessito de saber 1 total, no script antigo sempre que editasse ele ia criar um registo na data que editvada, dava para dar a voltar e recolher o ultimo registo do mês mas o querty fica vais complicado e ia ficar lixo na base de dados

#32

Se não te aparece nada nos atributos é porque a query está com erro, o HeidiSQL permite executares a query, assim vês de imediato o resultado.

#33

já coloquei a query da electricidade no heidisql mas nao apareçe nada acho q nao da assim para fazer copy e past ?

image
image1366×768 104 KB
?

#34

ja deu … faltava-me carregar no play … lol

#35

Como posso colocar legendas nos graficos: acrecentar em baixo a legenda dias e na altura horas, ?

image
image1366×768 100 KB

este é o codigo que tenho para o grafico:

chart: bar
custom_options:
  showLegend: false
data:
  datasets:
    - backgroundColor: '${states["sensor.horas_luzes"].attributes.colors}'
      borderWidth: 1
      data: '${states["sensor.horas_luzes"].attributes.data}'
      label: Tempo led strip ligado
  labels: '${states["sensor.horas_luzes"].attributes.labels}'
entity_row: false
options:
  scales:
    yAxes:
      - ticks:
          beginAtZero: true
  title:
    display: true
    text: >
      ${"Tempo Led strip Ligado -> " +
      states["sensor.horas_luzes"].attributes.total_horas_led + " horas"}
type: 'custom:chartjs-card'
#36

Assim:

options:
  scales:
    yAxes:
      - scaleLabel:
          display: true
          fontStyle: initial
          labelString: Horas
    xAxes:
      - scaleLabel:
          display: true
          fontStyle: initial
          labelString: Dias
2 Curtiram
#37

Viva,

O script q acompanha o tutorial permite que se atualize 2 ou mais colunas por interação?

Se sim, qual é a sintaxe do shell_command e da automação?

Obg

#38

@bbaixo Neste contexto o que entendes por por interação ?

#39

Interação = por alteração do estado da entidade

ou

por cada vez que a automação corre.

Detalhando, no exemplo do tutorial, é carregada na base de dados o valor da energia - uma variável, uma entidade - num dado momento.

Eu gostaria de carregar, pelos menos, 3 dados ao mesmo tempo, tendo em consideração que tenho um contador trifásico.

Pode, também haver interesse em carregar mais dados, como por exemplo a tensão e a corrente.

obg

#40

Nas actions da automação, metes 3 diferentes, apontando os 3 valores que queres

Será isso?

Copyright © 2017-2021. Todos os direitos reservados
CPHA.pt - info@cpha.pt

FAQ | Termos de Serviço/Regras | Política de Privacidade