Check the history with a graph showing state and sum, something is probably wrong there. I do not know what the energy dashboard uses (state or sum).
You imported hourly values? (So, one value every hour)? Thats necessary.
Thanks, Klaus. I imported one record per day at 00:00, using the same values for state and sum. There were some issues, most which I managed to correct manually by now (using developer-tools statistics). Just a few records at other times during the day seem to spoil the graph now.
Iām looking for an easy way to get rid of those records (using DB browser for SQLite) but Iām afraid to connect to the database while HA is running.
Regards, Frank
Hi Klaus,
I have a solar system that is not integrated into HA, but I can obtain monthly energy produced by my energy provider. I want to show this data into HA and I have used your integration to import data. Since there is no sensor associated and following your guide, I have used the āsensor:ā notation:
You can import:
- Either statistics for existing sensors (internal statistics). These sensors have a ā.ā in its name, e.g. sensor.sun_solar_azimuth
- If you try to import such a sensor which does not exist, you will see this sensor under developer tools / statistics, with an error. You can fix the error there, whereas fix means, remove it from database again
- Or statistics for not existing sensors (external statistics). These sensors have a ā:ā in its name, e.g. sensor:not_existing_sun_solar_azimuth
The sensor shows up in Developers Tools > Statistics, but I can not find it in any other place in HA.
Any suggestion on how to use it, for example, in a graph?
There are also some errors in the data imported (I have not specified the state value), how can I delete this data and import them again?
Thank you.
if itās visible in Statistics then itās most likely all OKā¦can you see the sensor under Developer Tools > States?
As far as I know if you reimport the same (but corrected) values they will just overwrite the existing entries and youāll get the result you want
No, if i search for it here no entity show up. I have looked for it everywhere, nothing found, it is visible only into Statistics.
how did you setup the Sensor entity so?
Under normal circumstances an entity is created and itās attributes (device_class and state_class) tell HA whether it has Statistics or not
TBH Iām not sure how youād create a Statistic without an Entity to start withā¦unless this integration is doing itā¦Iāve used it to add values to a template sensor I have and the imported data shows fine as an entity and as statisticsā¦
editā¦re-reading the GitHub notes for this maybe you have only created the statistics without an underlying sensor so that would be the issue (though Integration is functioning as designed)
from GitHub
you can import:
** Either statistics for existing sensors (internal statistics). These sensors have a ā.ā in its name, e.g. sensor.sun_solar_azimuth*
-
- If you try to import such a sensor which does not exist, you will see this sensor under developer tools / statistics, with an error. You can fix the error there, whereas fix means, remove it from database again*
** Or statistics for not existing sensors (external statistics). These sensors have a ā:ā in its name, e.g. sensor:not_existing_sun_solar_azimuth*
- If you try to import such a sensor which does not exist, you will see this sensor under developer tools / statistics, with an error. You can fix the error there, whereas fix means, remove it from database again*
so if youāve chosen the second option itās expected that no sensor getās created
there are Statistic Cards available in HA that let you use this data so that might be an alternate route for youā¦
I havenāt, following the guide into the integration page was specified that i can import the data in this way if the sensor do not exists.
Can you please share the code of your template sensor? As i know a template sensor show āmanipulated dataā of other sensor/s into itās state. What i have to put into the āstate templateā field of my helper template sensor for my use case?
Iāve edited my original post as you were replying so understand what youāve done nowā¦
what I did was create a blank/non-updating sensor with zero as current valueā¦made sure it had the right device/state class and then imported my history (to show old Energy usage)ā¦
the template sensor I have is created as a helper in HAā¦see setting I used below
I am not sure what you are trying to achieve. When you do not have āliveā data from a sensor, but just importing historical data with the integration, then importing with the ā:ā is totally fine. You do not need a template sensor then.
Then you āonlyā have statistic data, and you need to use statistic cards to show these data in the UI:
Plotly is also a good way to show graphs.
1 Like
Thanks! this integration saved me from losing 4 years of statistics regarding energy.
having about 20 sensors, losing everything was really a drama.
I created a script in python that can facilitate the creation of the tsv, starting from the name of the sensor (which measures the kwh).
maybe it will be useful to someone, it is very simple but with a few inputs it allows you to recreate the situation that was lost.
it is specific for the format: counter statistics with state/sum.
the only 2 changes to be able to use it:
database_path = ā/path/home-assistant_v2.dbā (locally on your PC)
filter_value = āsensor.consumo_energetico_piā
import sqlite3
import csv
def fetch_ids_with_filters(db_path, table_name, unit_column, unit_value, filter_column, filter_value):
try:
# Connessione al database SQLite
connection = sqlite3.connect(db_path)
cursor = connection.cursor()
# Query parametrizzata per evitare SQL injection
query = f"SELECT id FROM {table_name} WHERE {unit_column} = ? AND {filter_column} = ?"
cursor.execute(query, (unit_value, filter_value))
# Recupero degli ID
ids = [row[0] for row in cursor.fetchall()]
# Chiusura della connessione
cursor.close()
connection.close()
return ids
except sqlite3.Error as e:
print(f"Errore durante l'accesso al database: {e}")
return None
def fetch_statistics_with_metadata_id(db_path, table_name, metadata_id, statistic_id, unit_value):
try:
# Connessione al database SQLite
connection = sqlite3.connect(db_path)
cursor = connection.cursor()
# Query parametrizzata per filtrare metadata_id e sum non NULL
query = f"SELECT '{statistic_id}' AS statistic_id, '{unit_value}' AS unit, strftime('%d.%m.%Y %H:%M', start_ts, 'unixepoch') AS start, state, sum FROM {table_name} WHERE metadata_id = ? AND sum IS NOT NULL"
cursor.execute(query, (metadata_id,))
# Recupero dei dati
rows = cursor.fetchall()
# Recupero dei nomi delle colonne
column_names = [description[0] for description in cursor.description]
# Chiusura della connessione
cursor.close()
connection.close()
# Restituzione dei dati come lista di dizionari
return [dict(zip(column_names, row)) for row in rows]
except sqlite3.Error as e:
print(f"Errore durante l'accesso al database: {e}")
return None
def export_to_tsv(data, output_file):
try:
# Scrittura dei dati in formato TSV
with open(output_file, mode='w', newline='', encoding='utf-8') as file:
writer = csv.DictWriter(file, fieldnames=data[0].keys(), delimiter='\t')
writer.writeheader()
writer.writerows(data)
print(f"Dati esportati con successo in {output_file}.")
except Exception as e:
print(f"Errore durante l'esportazione dei dati: {e}")
if __name__ == "__main__":
# Specifica il percorso del database e il nome della tabella
database_path = "/path/home-assistant_v2.db"
table_name_meta = "statistics_meta"
table_name_stats = "statistics"
unit_column = "unit_of_measurement"
unit_value = "kWh"
filter_column = "statistic_id"
filter_value = "sensor.consumo_energetico_pi" # Cambia con il valore desiderato
output_file = "statisticdata.tsv"
# Recupera gli ID filtrati
ids = fetch_ids_with_filters(database_path, table_name_meta, unit_column, unit_value, filter_column, filter_value)
if ids:
print("ID trovati con unit_of_measurement = 'kWh' e statistic_id specificato:")
for id in ids:
print(id)
all_stats_data = []
# Esegui query sulla tabella statistics per ogni ID
for metadata_id in ids:
stats_data = fetch_statistics_with_metadata_id(database_path, table_name_stats, metadata_id, filter_value, unit_value)
if stats_data:
print(f"Dati trovati per metadata_id = {metadata_id}:")
for row in stats_data:
print(row)
all_stats_data.extend(stats_data)
else:
print(f"Nessun dato trovato per metadata_id = {metadata_id}.")
if all_stats_data:
export_to_tsv(all_stats_data, output_file)
else:
print("Nessun ID trovato o errore durante il download.")