Best approach to determine amount of time a device run on a given date

Configuration
1

Hi.

I’m trying to simplify the way I get the number of minutes a device was ‘on’. Currently, I’m using a SQL query to get the minutes from the SQLite DB, but I’m pretty sure there has to be another way.

So, my sensor template looks like this:

- platform: sql
  queries:
    - name: panel_minutes_today
      query: "SELECT (SUM(Interval) / 60) As panel_minutes FROM (SELECT CAST((JulianDay(T2.created) - JulianDay(T1.created)) * 24 * 60 * 60 As Integer) AS Interval FROM states T1 JOIN states T2 ON T1.entity_id = T2.entity_id AND T2.created > T1.created AND T2.state = 'off'  WHERE T1.entity_id = 'switch.panel_oficina' AND T1.state = 'on' AND date(T1.created) = date('now') GROUP BY T1.entity_id, T1.state, T1.created) t;"
      column: 'panel_minutes'

SQL query for easier reading:

SELECT (SUM(Interval) / 60) As panel_minutes FROM (
	SELECT CAST((JulianDay(T2.created) - JulianDay(T1.created)) * 24 * 60 * 60 As Integer) AS Interval 
	FROM states T1 
		JOIN states T2 ON T1.entity_id = T2.entity_id 
		AND T2.created > T1.created 
		AND T2.state = 'off'  
	WHERE T1.entity_id = 'switch.panel_oficina' 
	AND T1.state = 'on' 
	AND date(T1.created) = date('now') 
	GROUP BY T1.entity_id, T1.state, T1.created
) t;

As you might noticed, I use the states table and based on the state field (‘on’ and ‘off’) I count the seconds between the two dates, then convert to minutes.

This approach works but has a pitfall (probably more) and it is the scenario where the start date is on Date A but the end date is on Date B (where Date B = Date A + day).

Is there a better approach to get the time in minutes a device worked on a given date?

TIA

2

The builiting History Stats will do the heavy lifting for you. https://www.home-assistant.io/integrations/history_stats/

Same caveat as the way you are doing it – if you want “last 30 days of use” you need to keep your data that long in the HA DB. will bloat your HA database. I lot of folks go the influx/grafana route for that stuff.

1 Like
3

Yeah, I was thinking about that as well, since this will let me know how much do I spend on electricity on a monthly basis (for one device at least).

Thanks.

4

Let me get this straight.
You want one device monitored
You want data only for the current month and reset it (or move it to last month) at the start of a new month

(I’m guessing because of what you were doing on the other thread)

5

Hi @Mutt.

You got it!

I’m basically exploring my options in terms of power consumption, reporting, and calculations. That’s why I said Influx/Grafana is on the radar.

I started simple, with the device I’m controlling (heating panel) which has a constant power consumption as you read on the other post. This eliminates one variable.

The next step is going beyond one day and start generating reports for months and even years so I can compare power consumption and expenses.

Next, I’ll add an AC current meter (SCT-013) for the mains at home and will use what I already have, to calculate the whole house power consumption.

That’s basically the roadmap :slight_smile:

6

Okay that’s some heavy data storage, I just thought you might get away with something quick and dirty to give you today’s consumption, yesterday’s, this month so far, and last months.
But you want actual logging data so I’ll just back out at this point
Good luck with that though
Cheers

1 Like