Hi,
This was massively helpful. I have no background in SQL, Python or HA development but I’ve managed to make it work for my case.
The above python code did not initially work for me. Below are the issues I had:
- I use MariaDB so sqlite3 was not working (or at least that was my conclusion) and the SQL query fails where the 24-hour timestamp is calculated.
- My temperature data had “unavailable” states that were getting imported and messing up the plot.
- When CSV was being read, the temperature data is read as str. This causes the final plot y-axis to be out of order.
(1) and (2) were fixed by using MySQLdb instead of sqlite3 and changing the query to exclude “unavailable” states. The database connection is:
import MySQLdb
db_file = MySQLdb.connect("core-mariadb","username","password","homeassistant")
The SQL Query is constructed as below to omit “unavailable” records and the timedelta to get records from 24 hours ago is calculated outside the query string and substituted into it.
#Get timestamp for 24 hours ago
time_24h_ago = datetime.now() + timedelta(hours = -24)
time_24h_ago_timestamp = datetime.timestamp(time_24h_ago)
#Define the SQL query
sql_query_str = """
SELECT states.last_updated_ts, states.state, states_meta.entity_id
FROM states
JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.temperature'
AND states.state != "unavailable"
AND states.last_updated_ts >= %s
ORDER BY states.state_id ASC;
"""
Replace the %s with time_24h_ago_timestamp
sql_query = sql_query_str.replace("%s", str(time_24_ago_timestamp))
To convert the temperature from str to a float I used the dtype method in pandas to convert the temperature as its being read. The other two variables are imported as objects.
types_dict = {'timestamp' : object, 'temperature': float, 'entity_id' : object}
df = pd.read_csv(csv_file, names=['timestamp', 'temperature', 'entity_id'], dtype=types_dict)
Finally, I was interested in a 8-hour power usage chart, so I changed all of the above to use a power usage sensor.
So now I end up with this on Telegram. Massive thanks to your solution @raffler