Recommendations on monthly energy consumption

Configuration
1

Hi there!

I’ve been looking into showing monthly energy consumption for some time now. Right now I have a shelly 3em sending data into HA using the built in shelly integration. Then I have a daily , monthly and yearly utility meter running on the power consumption. Thats all great.

… But.

I would really like to have the power consumption available on monthly basis - not like 30 days but actual real months. So 12 columns going from January to December . I’ve been trying with influxdb and got flux working so I could split data based on 1mo and not just 30days like I’ve seen many do, but still … It’s not quite there.

So I really want to hear if someone has solved this problem and want to share how? Everything from back to frontend ideas/solutions are welcome! :slight_smile:

2

Hi
I’m also looking for such features since months, but never find any solution so far. Ieven tried using InfluxDB+Grafana, but no way…
Yes, if someone has an idea that would be great as it’s clearly something really missing there in HA Utility meter and/or Grafana too

1 Like
3

I have made something similar in Grafana.

image
image1866×968 105 KB

“Office power” comes from HA via the influxdb integration.

Here is the source of the dashboard, if that can help:

4

Hi @koying , what you are showing me is not per month basis. I want the data in a bar chart going from january to december .

5

Changing to monthly rather than hourly is trivial in Grafana.
Just trying to give hints, not a foolproof solution.

6

Hi @isaste , right now I’ve configured my recorder to send the daily utility meter data to a mariadb . Here I will try to do some old school sql magic and first group by day to find the max value pr. day and then group by month and find the total sum pr. month.

I’ve also hooked grafana up to this database and I will try to use this as the frontend :slight_smile:

7

I think both myself and @isaste have tried to group by month in grafana . Not 30 days , but actual months. And both of us failed. If you want to share how to do that it is much appreciated! :slight_smile:

8

Ah, sorry, missed that part.

Not possible in InfluxQL, but possible in Flux, apparently:
Group by Month and Year - Grafana Support - Grafana Labs Community Forums

My code use InfluxQL, so, indeed, won’t work for that use-case.

1 Like
9

@cvester have you managed to do it in the end? I’m in the same boat with shelly devices and utility meter for daily/monthly consumption, but I would like to be able to visualize it in grafana by month.
We do have that in ‘Energy’ part of the HA now, but I would like to have it in grafana as well.

10

I didn’t think Grafana had a monthly time grouping when using InfluxDB?

https://github.com/influxdata/influxdb/issues/3991#issuecomment-611327521

I’ve been using 30 days.

11

No it doesn’t, indeed. Clarified that a bit below.
Now, if you’re masochistic, you can likely achieve the same, with month grouping, in Flux :slight_smile:

12

I’ll try most things at least once :slight_smile:

I have no idea what Flux is though. Next week’s project. Maybe.

13

It’s what replaces InfluxQL as interrogation language.
It isn’t too far from jinja in some ways, but hard to grasp if you’re accustomed to SQL-like languages , or at least it is for me :wink:

14

Well that doesn’t sound so bad. SQL is voodoo to me but I can get by with jinja.

1 Like
15

@nightshadow , no I never really managed to get it grouped by month . I ended up grapping the numbers from my energy company’s website instead :smiley:

If I should make it now I think I would just make some sort of script that runs last day every month and grabs the current number into a database, then perhaps make some sort of derivative when selecting it from the DB .

I hope you figure it out :slight_smile:

1 Like
16

You could use a triggered template sensor for that.

template:
  - trigger:
      - platform: template
        value_template: "{{ (now() + timedelta(days=1)).day == 1 and now().strftime("%H:%M") == '23:59' }}" 
    sensor:
      - name: "Monthly Total Energy"
        state: "{{ states('sensor.your_energy_sensor_here') }}"
        unit_of_measurement: "kWh"
        device_class: energy

That will trigger at 23:59 on the last day of the month and set the value equal to your energy sensor.

2 Likes
17

So I would have the new ‘value_template’ entity which would update once a month only… And all I have to do is put that in grafana… :slight_smile:
Simple solution, but it is what I need exactly :wink: Thanks!

1 Like
18

(actually it’s sensor.monthly_total_energy) :smiley:

19

Hi Tom,

It doesn’t seem to work for me…

This is the code I’m using:

  - trigger:
      - platform: template
        value_template: "{{ (now() + timedelta(days=1)).day == 1 and now().strftime('%H:%M') == '23:59' }}" 
    sensor:
      - name: "Monthly Total Energy"
        state: "{{ states('sensor.monthly_energy') }}"
        unit_of_measurement: "kWh"
        device_class: energy

But the entity sensor.monthly_total_energy has ‘unknown’ state.
sensor.monthly_energy is the entity from utility meter that’s calculating monthly energy usage of my shelly 1PM sensor.
Any idea what’s wrong?

20

It won’t have a value until 23:59 on the last day of the month. When the trigger occurs.