Hi,
I need those Informations like consumption doday, yesterday, this week, last week and so on…
For that, I can use the Statistic Card. But I want so add this Informations in the Fold Entity Row Card. And for my Informations, this card need the Information as an Entity.
Now I have thought, to create helper sensor for all this Stats. But how can I extract the Statistic Data from my consumption Entity (eg. Energy)?
From what I know, only by querying the recorder via SQL integration…you’d need to create a few sensors then with that.
I suspected it. 
There is probably no easier way?
Here is my first solution with sql queries. It takes stats from an entity. All of theres have an inner join to consider the last state of previous day/week/month/quarter/year.
- platform: sql
db_url: mysql://user:pass@core-mariadb/homeassistant?charset=utf8
queries:
- name: Wasserverbrauch in L (heute)
query: 'select IFNULL(ROUND(max(states.state) - max(max_last_day.state), 2), 0) AS state from states INNER JOIN (select IFNULL(ROUND(max(state), 2), 0) AS state from states where state > 0 AND entity_id LIKE "sensor.wasserverbrauch_l" AND DATE(last_updated) = CURDATE() - 1) AS max_last_day where states.state > 0 AND states.entity_id LIKE "sensor.wasserverbrauch_l" AND DATE(states.last_updated) = CURDATE();'
column: "state"
unit_of_measurement: L
- name: Wasserverbrauch in L (gestern)
query: 'SELECT Ifnull(Round(Max(states.state) - IF(max(max_last_day.state) > 0, max(max_last_day.state), min(states.state)), 2), 0) AS state FROM states INNER JOIN ( SELECT Ifnull(Round(Max(state), 2), 0) AS state FROM states WHERE state > 0 AND entity_id LIKE "sensor.wasserverbrauch_l" AND Date(last_updated) = Curdate() - 2) AS max_last_day where states.state > 0 AND states.entity_id LIKE "sensor.wasserverbrauch_l" AND Date(states.last_updated) = Curdate() - 1;'
column: "state"
unit_of_measurement: L
- name: Wasserverbrauch in L (diese Woche)
query: 'select IFNULL(ROUND(max(states.state) - IF(max(max_last_day.state) > 0, max(max_last_day.state), min(states.state)), 2), 0) AS state from states INNER JOIN (select IFNULL(ROUND(max(state), 2), 0) AS state from states where state > 0 AND entity_id LIKE "sensor.wasserverbrauch_l" AND YEARWEEK(last_updated, 1) = YEARWEEK(CURRENT_DATE - INTERVAL 1 WEEK, 1)) AS max_last_day where states.state > 0 AND states.entity_id LIKE "sensor.wasserverbrauch_l" AND YEARWEEK(states.last_updated, 1) = YEARWEEK(CURRENT_DATE, 1);'
column: "state"
unit_of_measurement: L
- name: Wasserverbrauch in L (letzte Woche)
query: 'SELECT Ifnull(Round(Max(states.state) - IF(Max(max_last_day.state) > 0, Max(max_last_day.state), Min(states.state)), 2), 0) AS state FROM states INNER JOIN ( SELECT Ifnull(Round(Max(state), 2), 0) AS state FROM states WHERE state > 0 AND entity_id LIKE "sensor.wasserverbrauch_l" AND Yearweek(last_updated, 1) = Yearweek(CURRENT_DATE - INTERVAL 2 week, 1)) AS max_last_day where states.state > 0 AND states.entity_id LIKE "sensor.wasserverbrauch_l" AND Yearweek(states.last_updated, 1) = Yearweek(CURRENT_DATE - INTERVAL 1 week, 1);'
column: "state"
unit_of_measurement: L
- name: Wasserverbrauch in L (dieser Monat)
query: 'select IFNULL(ROUND(max(states.state) - IF(max(max_last_day.state) > 0, max(max_last_day.state), min(states.state)), 2), 0) AS state from states INNER JOIN (select IFNULL(ROUND(max(state), 2), 0) AS state from states where state > 0 AND entity_id LIKE "sensor.wasserverbrauch_l" AND MONTH(last_updated) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) and YEAR(last_updated) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)) AS max_last_day where states.state > 0 AND states.entity_id LIKE "sensor.wasserverbrauch_l" AND MONTH(states.last_updated) = MONTH(CURRENT_TIMESTAMP) and YEAR(states.last_updated) = YEAR(CURRENT_TIMESTAMP);'
column: "state"
unit_of_measurement: L
- name: Wasserverbrauch in L (letzter Monat)
query: 'SELECT Ifnull(Round(Max(states.state) - IF(Max(max_last_day.state) > 0, Max(max_last_day.state), Min(states.state)), 2), 0) AS state FROM states INNER JOIN ( SELECT Ifnull(Round(Max(state), 2), 0) AS state FROM states WHERE state > 0 AND entity_id LIKE "sensor.wasserverbrauch_l" AND Month(last_updated) = Month(CURRENT_DATE - INTERVAL 2 month) AND Year(last_updated) = Year(CURRENT_DATE - INTERVAL 2 month)) AS max_last_day where states.state > 0 AND states.entity_id LIKE "sensor.wasserverbrauch_l" AND Month(states.last_updated) = Month(CURRENT_DATE - INTERVAL 1 month) AND Year(states.last_updated) = Year(CURRENT_DATE - INTERVAL 1 month);'
column: "state"
unit_of_measurement: L
- name: Wasserverbrauch in L (dieses Quartal)
query: 'select IFNULL(ROUND(max(states.state) - IF(max(max_last_day.state) > 0, max(max_last_day.state), min(states.state)), 2), 0) AS state from states INNER JOIN (select IFNULL(ROUND(max(state), 2), 0) AS state from states where state > 0 AND entity_id LIKE "sensor.wasserverbrauch_l" AND QUARTER(last_updated) = QUARTER(CURRENT_DATE - INTERVAL 1 QUARTER) and YEAR(last_updated) = YEAR(CURRENT_DATE - INTERVAL 1 QUARTER)) AS max_last_day where states.state > 0 AND states.entity_id LIKE "sensor.wasserverbrauch_l" AND QUARTER(states.last_updated) = QUARTER(CURRENT_TIMESTAMP) and YEAR(states.last_updated) = YEAR(CURRENT_TIMESTAMP);'
column: "state"
unit_of_measurement: L
- name: Wasserverbrauch in L (letztes Quartal)
query: 'SELECT Ifnull(Round(Max(states.state) - IF(Max(max_last_day.state) > 0, Max(max_last_day.state), Min(states.state)), 2), 0) AS state FROM states INNER JOIN ( SELECT Ifnull(Round(Max(state), 2), 0) AS state FROM states WHERE state > 0 AND entity_id LIKE "sensor.wasserverbrauch_l" AND Quarter(last_updated) = Quarter(CURRENT_DATE - INTERVAL 2 quarter) AND Year(last_updated) = Year(CURRENT_DATE - INTERVAL 2 quarter)) AS max_last_day where states.state > 0 AND states.entity_id LIKE "sensor.wasserverbrauch_l" AND Quarter(states.last_updated) = Quarter(CURRENT_DATE - INTERVAL 1 quarter) AND Year(states.last_updated) = Year(CURRENT_DATE - INTERVAL 1 quarter);'
column: "state"
unit_of_measurement: L
- name: Wasserverbrauch in L (dieses Jahr)
query: 'select IFNULL(ROUND(max(states.state) - IF(max(max_last_day.state) > 0, max(max_last_day.state), min(states.state)), 2), 0) AS state from states INNER JOIN (select IFNULL(ROUND(max(state), 2), 0) AS state from states where state > 0 AND entity_id LIKE "sensor.wasserverbrauch_l" AND YEAR(last_updated) = YEAR(CURRENT_TIMESTAMP) - 1) AS max_last_day where states.state > 0 AND states.entity_id LIKE "sensor.wasserverbrauch_l" AND YEAR(states.last_updated) = YEAR(CURRENT_TIMESTAMP);'
column: "state"
unit_of_measurement: L
- name: Wasserverbrauch in L (letztes Jahr)
query: 'select IFNULL(ROUND(max(states.state) - IF(max(max_last_day.state) > 0, max(max_last_day.state), min(states.state)), 2), 0) AS state from states INNER JOIN (select IFNULL(ROUND(max(state), 2), 0) AS state from states where state > 0 AND entity_id LIKE "sensor.wasserverbrauch_l" AND YEAR(last_updated) = YEAR(CURRENT_TIMESTAMP) - 2) AS max_last_day where states.state > 0 AND states.entity_id LIKE "sensor.wasserverbrauch_l" AND YEAR(states.last_updated) = YEAR(CURRENT_TIMESTAMP) - 1;'
column: "state"
unit_of_measurement: L
OK, well… one thing though, this information in table ‘states’ is called history where long term statistics is in table ‘statistics’ but since you look at a period shorter than your db purge, this will work too, it just looks at way more records than in statistics table.
Also here I donot know another way to extract them the way you do above
Ok, thanks for that information. I will have a look, if i can optimize my queriers by using the statistics table.
Edit, okay it looks my easier with statistics table. Example for last month:
SELECT Ifnull(round(max(sum), 2), 0) as states FROM `statistics` LEFT JOIN statistics_meta ON statistics_meta.id = statistics.metadata_id WHERE statistics_meta.statistic_id LIKE 'sensor.wasserverbrauch_l' AND Month(statistics.start) = Month(CURRENT_DATE - INTERVAL 1 month) AND Year(statistics.start) = Year(CURRENT_DATE - INTERVAL 1 month);