Recommendations on monthly energy consumption

Configuration
21

I know, I put that code in my HA one month ago, but it didn’t update on the last day of Feb.

22

Damn. It will also lose the state when you restart Home Assistant. Sorry.

23

Any way to make it permanent? :slight_smile:

24

i suggest using Input Helpers to store

25

I realize this has not been used some months ago, however I have the same question/problem ahead of me. So did you find any solution to this?
My idea would be to use a sensor with attributes and end of month just set that attribute.

26

Hi @flautze , I’m trying to move away from the manual build/code it yourself stuff because I think there’s too much maintenance with it.

However - In the meantime I made some code for my water meter . It takes the current number for a daily meter throughout the day and exports/overwrites that specific day in my own mysql database table. Plain and simple one row for each day.
After that I can easily group by month with some SQL.

When thats said I wouldn’t suggest it for others now where the energy dashboard is out. It has the ability to group by month just as I was searching for.

27

@cvester Ok, thanks for the answer.
After some thought I believe that is probably the easiest solution. But Inhave some questions :sunglasses:

For database are you using an addon to HA or do you use a separate VM/device?

How do you write to the database, is your code in an automation? Would you be willing to share it?

I believe if you are using utility meters you could maybe also just write the attribute „previous period“ to your sql for the previous day.

28

Triggered template sensors are now restored after a restart so this should work.

29

yes I am using the HA addon and are running a cron job on the host that calls a procedure in the database:

mysql -u homeassistant -e 'call import_daily_water();' -D prod -h "192.168.0.11" --password=qwerty

Heres the procedure:

CREATE DEFINER=`homeassistant`@`%` PROCEDURE `import_daily_water`()
BEGIN
	REPLACE INTO prod.daily_water
	SELECT FROM_UNIXTIME(hs.last_updated_ts) as DATE, 
	max(hs.state) as M3
	FROM homeassistant.states as hs
	where hs.entity_id like 'sensor.daily_water_mono'
	group by date(FROM_UNIXTIME(hs.last_updated_ts));
END

And the daily water table:

CREATE TABLE `daily_water` (
  `date` datetime NOT NULL,
  `m3` decimal(4,3) DEFAULT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I hope its useful for you.

I didnt know about the “previous period” thanks for the hint .