Energy dashboard: how to use the costs as entity?

[JazekerXX]

has anyone an idea how to get the cost values presented in the energy dashboard?
i would really love to use these, instead of making template sensors and do the calculations another time for myself…

[tom_l]

These calculations are not available as entities.

[jeedewee (Jurgen de Wijs)]

I used an SQL sensor to retrieve the costs and make them available as a sensor.

For example to get the costs of the daily energy in the previous month I could use:

sql: 
  - name: "Energie verbruikt door laadpaal in de vorige maand"
    unique_id: {some unique UUID}
    unit_of_measurement: "€"
    device_class: monetary
    query: >
      SELECT
        max(statistics.sum) - min(statistics.sum) AS sum
      FROM
        statistics
        INNER JOIN statistics_meta ON
          statistics.metadata_id = statistics_meta.id
      WHERE
        statistics_meta.statistic_id = 'sensor.electricity_meter_energy_consumption_tarif_1_cost'
        AND start_ts >= unixepoch('now', 'start of month', '-1 month', 'utc')
        AND start_ts <= unixepoch('now', 'start of month', 'utc')
    column: "sum"

You can find the entity id to enter in the Developer/Statistics screen and search for the corresponding cost entity.

I checked with the value in the energy dashboard and it matches exactly.

[JazekerXX]

Extract it from the database, hmmm. that’s smart!

[JazekerXX]

Too bad, but i think they should be available.
maybe in the next version

[tom_l]

Vote here:

Using values of energy management in dashboards

I love the new energy management feature as it is very similar to what I am already using (though significantly easier). As I use some features in existing dashboards I was wondering how I could use the values in existing dashboards without the need to use the energy cards themselves. Would it be possible to extract those values from the database or something? See below an example which basically shows the energy consumption since midnight. [Screenshot_20210912-075321]

[JazekerXX]

@tom_l

Vote here:

I love the new energy management feature as it is very similar to what I am already using (though significantly easier). As I use some features in existing dashboards I was wondering how I could use the values in existing dashboards without the need to use the energy cards themselves. Would it be possible to extract those values from the database or something? See below an example which basically shows the energy consumption since midnight. [Screenshot_20210912-075321]

good suggestion! let’s vote for that immediately

[al.rdb]

Hi,

I modified the query to show last month and this month consumption. I’m no DEV/IT - just played with epoch for a bit. Not sure if it’s correct, but it works.

configuration.yaml

sql: 
  - name: "Energy Cost SQL - previous month"
    unique_id: energy_cost_sql_prev
    unit_of_measurement: "kWh"
    device_class: energy
    query: >
      SELECT
        max(statistics.sum) - min(statistics.sum) AS sum
      FROM
        statistics
        INNER JOIN statistics_meta ON
          statistics.metadata_id = statistics_meta.id
      WHERE
        statistics_meta.statistic_id = 'sensor.myenergi_30cdw_charger_grid_import_today_2'
        AND start_ts >= unixepoch('now', 'start of month', '-1 month', 'utc')
        AND start_ts <= unixepoch('now', 'start of month', 'utc')
    column: "sum"
    
  - name: "Energy Cost SQL - this month"
    unique_id: energy_cost_sql_this
    unit_of_measurement: "kWh"
    device_class: energy
    query: >
      SELECT
        max(statistics.sum) - min(statistics.sum) AS sum
      FROM
        statistics
        INNER JOIN statistics_meta ON
          statistics.metadata_id = statistics_meta.id
      WHERE
        statistics_meta.statistic_id = 'sensor.myenergi_30cdw_charger_grid_import_today_2'
        AND start_ts >= unixepoch('now', 'start of month', 'utc')
        AND start_ts <= unixepoch('now', 'start of month', '+1 month', 'utc')
    column: "sum"