SQL query to get last 30 days energy use

Configuration
1

Hi,

I am trying to set up some SQL sensor which will calculate the averaged daily use of energy over the last 30 days. I have had a bunch of these running as described in my post but they are not great – the state column changes by large amounts due to glitches or some resets and you end up with -1100 kWh hours energy use.

I’ve since realised that it’s better to track the sum column in the LTS database. But since the sum column is not necessarily normalised the same as the current state, you have to find the difference in the database between the latest entry and the one 30 days ago. This leads to a somewhat complex query.

The following does work:

- name: Lights all Energy l30d
  unit_of_measurement: kWh
  query: >
    SELECT 
      (SELECT 
        sum 
        FROM (SELECT id, start_ts, sum FROM "statistics" WHERE metadata_id=
                (SELECT id FROM "statistics_meta" WHERE statistic_id='sensor.lights_all_energy')
              )
        WHERE 
          id= (SELECT MAX(id) 
                FROM (SELECT id FROM "statistics" WHERE metadata_id=
                      (SELECT id FROM "statistics_meta" WHERE statistic_id='sensor.lights_all_energy')
                      ) 
                LIMIT 1
              )
        LIMIT 1
      ) 
    - 
      (SELECT 
        sum 
        FROM (SELECT id, start_ts, sum FROM "statistics" WHERE metadata_id=
                    (SELECT id FROM "statistics_meta" WHERE statistic_id='sensor.lights_all_energy')
                  )
        WHERE
          start_ts >= UNIXEPOCH("now","-30 days","-2 hours") AND start_ts < UNIXEPOCH("now","-30 days","-1 hour") 
        LIMIT 1
      )
    AS change
  column: change
  value_template: "{{(value/30)|round(1)}}"
  unique_id: xx98e4tyj93fey4ft9387y4ht367

but doesn’t seem very efficient, at least to write it down. HA’s SQL does not seem to support the AS variable definition command and my SQL is just hacked from what I’ve read on stack exchange.

Would anyone have a suggestion for how to make this query more efficient (e.g. not look the db multkple times?)

Thanks!

2

I use SQL in my day job, but have never used it in HA… But am happy to take a stab in the dark :slight_smile:

Can you at least do a join between statistics and statistics_meta, rather than the inner select to get the metadata_id?

You shouldn’t need to do the SELECT sum FROM (SELECT…) part either, the inner SELECT id, start_ts, sum could just be SELECT sum FROM statistics JOIN statistics_meta… WHERE … etc

If that makes sense… Not sure how much more efficient it would be, but might read a little easier.

3

You may also be able to use an order by id in the first part to achieve the same effect as looking up the max id separately.

4

Thanks! I’ve thought about this and made:

    SELECT
      (SELECT 
        statistics.sum 
      FROM 
        statistics 
        INNER JOIN statistics_meta ON 
          statistics.metadata_id=statistics_meta.id 
        WHERE 
          statistics_meta.statistic_id='sensor.electricity_uncategorised_energy' 
        ORDER BY statistics.start_ts DESC 
      LIMIT 1)
    -
      (SELECT
        statistics.sum 
      FROM 
        statistics 
        INNER JOIN statistics_meta ON 
          statistics.metadata_id=statistics_meta.id 
        WHERE 
          statistics_meta.statistic_id='sensor.electricity_uncategorised_energy' 
        AND
          start_ts > UNIXEPOCH("now","-30 days","-1 hour")  
        LIMIT 1)
    AS change
1 Like
5

You might just want an order by statistics.start_ts ASC in the second query, to make sure you get the oldest record.