How do create a graph of how long a device was on for the day, week, month?

Tutorials & Examples
1

Beginner here. I’d like to be able to calculate usage (time on) for some of my devices. Can someone tell me what I need to do to accomplish this? I know that I’ll need to use persistence to log the switch states, but then what? Any help is most appreciated!

2

See:

https://communityshare.mitre.org/sites/p_and_p/policy_pages/2/2_5_7.aspx

1 Like
3

I’ve been playing around with various scripts and I’m not getting what I want. I’m logging my HVAC on/off times (variable HVACRuntimeC) in mysql like this:

mysql> select * from item16;
+---------------------+-------+
| Time                | Value |
+---------------------+-------+
| 2016-06-27 15:28:30 |     1 |
| 2016-06-27 15:36:37 |     0 |
| 2016-06-27 15:50:37 |     1 |
| 2016-06-27 16:19:42 |     0 |
| 2016-06-27 16:32:42 |     1 |
| 2016-06-27 16:41:42 |     0 |
| 2016-06-27 16:54:42 |     1 |
| 2016-06-27 17:02:43 |     0 |
| 2016-06-27 17:16:43 |     1 |
| 2016-06-27 17:24:43 |     0 |
| 2016-06-27 17:35:43 |     1 |
| 2016-06-27 18:01:43 |     0 |
| 2016-06-27 18:23:43 |     1 |
| 2016-06-27 18:33:43 |     0 |
| 2016-06-27 18:44:44 |     1 |
| 2016-06-27 18:59:44 |     0 |
| 2016-06-27 19:19:44 |     1 |
| 2016-06-27 19:29:44 |     0 |
| 2016-06-27 20:03:44 |     1 |
| 2016-06-27 20:10:44 |     0 |
| 2016-06-27 21:42:45 |     1 |
| 2016-06-27 23:11:46 |     0 |
| 2016-06-27 23:53:46 |     1 |
| 2016-06-28 00:58:47 |     0 |
| 2016-06-28 01:28:47 |     1 |
| 2016-06-28 01:46:48 |     0 |
| 2016-06-28 02:26:53 |     1 |
| 2016-06-28 02:38:53 |     0 |
| 2016-06-28 03:14:58 |     1 |
| 2016-06-28 03:28:59 |     0 |
| 2016-06-28 04:05:09 |     1 |
| 2016-06-28 04:17:09 |     0 |
| 2016-06-28 05:00:09 |     1 |
| 2016-06-28 05:10:10 |     0 |
| 2016-06-28 07:09:20 |     1 |
| 2016-06-28 07:15:20 |     0 |
+---------------------+-------+
36 rows in set (0.00 sec)

I’m trying to sum the run times like this:

HVACRuntimeD.postUpdate(HVACRuntimeC.sumSince(now.toDateMidnight, "mysql"))

This is giving me a sum total of all the times the unit turned on/off instead of calculating the actual time it is on. I’m really new to all this and am having a hard time pulling the pieces from the different scripts. It appears that I have the pieces I need, I just don’t know what command I need to use to total the times. Any help is most appreciated. Thanks for your patience.

4

When you run sumSince all it is doing is adding up the states. The time is not involved at all.

You will need to change your persistence to save the state every minute, instead of every change. Then when you do the sumSince you will get the number of minutes since there will be a 1 in the DB for every minute it was on.

1 Like
5

Thanks. I’ll modify it to check each minute and record when it turns on only.

6

These are the rules I’m using:

import org.openhab.core.library.types.*
import org.openhab.core.persistence.*
import org.openhab.model.script.actions.*
import org.joda.time.*


// Change HVAC Counter
rule "Change HVAC Counter"
when 
	Time cron "0 0/1 * * * ?"   // every minute
then
if (HVAC_Operating_State.state==2)
	{
		postUpdate(HVACRuntimeC, 1)
	} 
end

// Record Total Daily HVAC runtimes
rule "Record HVAC Runtime" 
when 
Time cron "0 0/1 * * * ?"
then
	HVACRuntimeD.postUpdate(HVACRuntimeC.sumSince(now.toDateMidnight, "mysql"))
	HVACRuntimeW.postUpdate(HVACRuntimeC.sumSince(now.toDateMidnight.withDayOfWeek(1), "mysql"))
end

My thinking is that this will run once per minute, and if HVAC_Operating_State.state is equal to 2 at that time then it will update HVACRuntimeC with a 1 in the MYSQL database. Unfortunately this does not do anything. I’m not sure what’s going on. All variables are Number type. Persistence is set to everyChange strategy.

Wow. I’m pretty terrible at this!

7

That is not what I am recommending.

You already have HVACRuntimeC being stored in the database with 1s for on and 0s for off.

So update your mysql.persist file to save that Item every minute instead of on every change? This way if you count the 1s you have counted the minutes it has been On. If you want a better resolution than every minute (and don’t mind trading the space in your DB for simplicity) change it to every second.

No need for rules or other Items and any other changes.

Example everyMinute strategy from my setup.

// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
        // for rrd charts, we need a cron strategy
        everyMinute : "0 * * * * ?"

        default = everyChange
}

Items {

        // additionally persist weather info every minute
        gHistory*,gChart* : strategy = everyMinute
}

Depending on your mysql.persist file config, it may be only saving everyChange in which case postUpdate values will not be saved unless the update results in a change.

8

This was the case. I’m happy to report that putting things back the way they were before and changing to everyMinute fixed things! I can’t thank you enough for your help.

Thinking ahead, I guess I should consider some kind of house keeping as I’m sure the database may grow out of control if I leave things the way they are now. Do you have any suggestions on a good approach?

9

The fact you are using MySQL verses one of the embedded DBs puts you in a better position than most. I’d create a cron job that runs a script that clears out any data older than a certain age periodically. How often and how old the data must be depends largely on what you are doing with this data.

Personally I use rrd4j. I don’t really care if the precision of the data gets worse as the data ages and it is a small price to pay to have a DB that I don’t have to clear out.

Another alternative, more work but less DB intensive, is to write rules to keep a running total of your useage. It is not terrible easy to do but there is an example here:

1 Like
10

This is working great. As with everything, I’m twiddling with it a bit more and would like to be able to display usage for yesterday. I’ve been noodling around to try and figure out a way to format the sumsince to add up values only for yesterday. Is there an easy way to do this with joda time? I can’t see anything explicit but I’d guess it would be something similar to this:

HVACRuntimeY.postUpdate(HVACRuntimeC.sumSince(now.minusDays(1).toDateMidnight))

Unfortunately this not working. I’m not getting an error but the value showing up is 0 …

11

Calculate the sum for the past two days and then subtract the sum for today.

val twoDays = HVACRuntimeC.sumSince(now.minusDays(1).toDateMidnight))
val today =HVACRuntimeC.sumSince(now.toDateMidnight)
val yesterday = twoDays - today
1 Like