Homematic switch actuator summarize the time it is online

un1k · August 7, 2018, 4:37pm

Hello community, I have a setup with a lot of homematic devices in my house. I have homematic based switches which I can switch on and off on my Sitemap an they all are persistent with my mysql persistence so I can see when they have been switched on or off. Now I want to summarize the time they where switched on during a week. For example I want to see how long the switch for the children’s TV was switched on during a week in hours. The week begins on Monday morning and ends Sunday night. I have no idea to get these values. I want to write a rule which sends a pushover when the TV was more than 10 hours a week on for example. Can you please help me to get an entry to this development plan? Any idea?

rlkoshak · August 7, 2018, 9:02pm

Sadly, this is not something that OH is particularly good at. I can see three strategies for this.

Install InfluxDB and use an everyMinute strategy on these Items (you can have more than one persistence database at the same time). Then in a Rule you can do something like

val numDays = now.getDayOfWeek
val minsOn = TV.sumSince(now.minusDays(numDays).withTimeAtStartOfDay, "influxdb")

This works because InfluxDB saves switch states as 1 for ON and 0 for OFF in the database (unlike MySQL) and because we save everyMinute we know how many minutes the TV was on by counting the number of 1s in the DB over the given time period. And getDayOfWeek returns the day of the week as a number with Sunday being 0 so if we go back that number of days we are going back to Sunday this week. The withTimeAtStartOfDay gets us to midnight on Sunday. This is probably the easiest to implement from a Rules perspective.

tl;dr count the number of 1s in the DB since midnight on Sunday of this week.

@5iver posted a solution that uses the REST API to query the database and then parses the time stamps to calculate the ON time. See Time in state for an item - using persistence
Keep track of the time in Rules using unbound Items or global variables.

rule "Calculate TV runtime"
when
    Item TV changed
then
    if(previousState == NULL) return; // ignore changes from NULL

    if(TV.state == OFF) {
        val lastOn = (TV_ON_Time.state as DateTime).getZonedDateTime.toEpochSecond * 1000
        val totalRuntime = TV_Runtime_Millis.state as Number + (now.millis - lastOn)
        TV_Runtime_Mills.postUpdate(TV_Runtime_Millis.state as Number + (now.millis - lastOn))

        if(totalRuntime > 1000*60*60*10) { // 10 hours in millis
            // alert
        }
    }
    else {
        TV_ON_Time.postUpdate(new DateTimeType)
    }
end

rule "Reset TV runtime"
when
    Time cron "..." // use the Quarts cron builder to have this run at midnight every Sunday
then
    TV_Runtime_Millis.postUpdate(0)
    if(TV.state == ON) TV_ON_Time.postUpdate(new DateTimeType)
end

I just typed this in from memory. I’m sure there are errors.

The theory of operation is when the TV turns OFF save a timestamp in an Item (use persistence with restoreOnStartup so the time is preserved when OH restarts or you modify Items).

When the TV turns OFF calculate the number of milliseconds since the TV turned ON (using the previously saved timestamp) and add that to the total runtime Item.

At midnight on Sunday, reset the total runtime Item back to 0 and if the TV happens to be on at that time, change the TV ON timestamp to now.

The above will only work if you have persistence on these Items with restoreOnStartup and you will have to bootstrap the initial value of the timestamp Item and the total runtime Item with initial values or else the Rule will fail. You can either give them initial values through the REST API or create a temporary System started Rule to populate the Item that you can then remove and let persistence with restoreOnStartup populate them from now on.

I plan on writing these three options up as a DP at some point. I just haven’t had the time.

un1k · August 10, 2018, 11:37am

Hello rikoshak,
thanks for your help. I testet your solution no.1 with the influxdb but I alwaya get a 0 as answer. I testet with one hour in the past, later then with the hole week. Here is my rule:

val numDays = now.getDayOfWeek
// val minsOnLeonie = actuator_office_2_ch2.sumSince(now.minusDays(numDays).withTimeAtStartOfDay, "influxdb")
val Number minsOnLeonie = actuator_office_2_ch2.sumSince(now.minusHours(1), "influxdb")

rule "tv_on_leonie"
when
    Time cron "0 0/1 * * * ?"
    //Item actuator_office_2_ch2 changed
then
    logInfo ( "FILE", "numDays: "+ numDays )
    logInfo ( "FILE", "total tv time since last hour " + minsOnLeonie )
end

Every minute I get a 0 in my Logfile as result but in the influx database is a new entry every minute.
In the openhab.log I found the following error:

Cannot reference the field 'actuator_office_2_ch2' before it is defined

Can you please help me?
thanks

rlkoshak · August 10, 2018, 1:39pm

The lines that define globals at the top of a Rules file only get executed once when the .rules file is loaded. So you have two problems.

That line where you define minsOnLeonie gets executed before your Item actuator_office_2_ch2 is loaded and added to the registry.
Even if you didn’t have problem 1, minsOnLeonie would only have the value from when you loaded the .rules file. The same goes for numDays. You need to define and populate those variables inside your Rule so they get executed when the Rule runs, not just when the .rules file loads.

un1k · August 10, 2018, 4:06pm

thanks.
I put the declaration inside my rule but nothing changed. I expected to see the sum of all minutes from the last hour. The switch is on and I get no error in the log file but a “0” as result.

rule "tv_on_leonie"
when
    Time cron "0 0/1 * * * ?"
    //Item actuator_office_2_ch2 changed
then
  val numDays = now.getDayOfWeek
  val Number minsOnLeonie = actuator_office_2_ch2.sumSince(now.minusHours(1), "influxdb")
  logInfo ( "FILE", "numDays: "+ numDays )
  logInfo ( "FILE", "total tv time since last hour " + minsOnLeonie )
end

rlkoshak · August 10, 2018, 5:07pm

Verify that InfluxDB has 1s for that Item stored.

un1k · August 11, 2018, 5:45am

I checked my influx database and examined the results. Every minute you can see an entry with a “1” when the switch is turned on. I switched to “precision rfc3339” in the database to make the timestamps human readable. The database saves the entries with the UTC timezone.
See…

2018-08-10T17:33:00.014Z 1
2018-08-10T17:34:01.908Z 1
2018-08-10T17:35:00.02Z  1
2018-08-10T17:36:00.004Z 1
2018-08-10T17:37:00.003Z 1
2018-08-10T17:38:00.017Z 1
2018-08-10T17:39:00.017Z 1
2018-08-10T17:40:00.003Z 1
2018-08-10T17:41:00.014Z 1
2018-08-10T17:42:00.004Z 1
2018-08-10T17:43:00.002Z 1
2018-08-10T17:44:00.016Z 1
2018-08-10T17:45:01.909Z 1
2018-08-10T17:46:00.019Z 1
2018-08-10T17:47:00.002Z 1
2018-08-10T17:48:00.03Z  1
2018-08-10T17:49:00.003Z 1
2018-08-10T17:50:00.008Z 1
2018-08-10T17:51:00.014Z 1
2018-08-10T17:52:00.016Z 1
2018-08-10T17:53:00.003Z 1
2018-08-10T17:54:01.873Z 1
2018-08-10T17:55:00.003Z 1
2018-08-10T17:56:00.018Z 1
2018-08-10T17:57:00.005Z 1
2018-08-10T17:58:00.003Z 1
2018-08-10T17:59:00.024Z 1
2018-08-10T18:00:00.002Z 1
2018-08-10T18:01:00.014Z 1
2018-08-10T18:02:00.01Z  1
2018-08-10T18:03:00.004Z 1
2018-08-10T18:04:00.025Z 1

Here is again my rule because there must be a mistake in this rule. To get the values from my influxdb we added in the variable declaration … , “influxdb”
Is that a special name? I have a mysql and influx persistence both in use.

rule "tv_on_leonie"
when
    Time cron "0 0/1 * * * ?"
    //Item actuator_office_2_ch2 changed
then
  val numDays = now.getDayOfWeek
  val Number minsOnLeonie = actuator_office_2_ch2.sumSince(now.minusHours(8), "influxdb")
  val minsOnLeonieWeek = actuator_office_2_ch2.sumSince(now.minusDays(numDays).withTimeAtStartOfDay, "influxdb")
  logInfo ( "FILE", "numDays: "+ numDays )
  logInfo ( "FILE", "total tv time since last 8 hours " + minsOnLeonie )
  logInfo ( "FILE", "total tv time since last week " + minsOnLeonieWeek )
end

And here is the output of the log file

2018-08-11 07:43:00.179 [INFO ] [.eclipse.smarthome.model.script.FILE] - numDays: 6
2018-08-11 07:43:00.181 [INFO ] [.eclipse.smarthome.model.script.FILE] - total tv time since last 8 hours 0
2018-08-11 07:43:00.182 [INFO ] [.eclipse.smarthome.model.script.FILE] - total tv time since last week 0

I don’t know what is wrong in my rule. The only filled variable is “numDays”.

rlkoshak · August 11, 2018, 2:17pm

There might be a bug introduced to oh at dinner point that broke the sumSince method. What you have in the rule looks correct.

You may have to use one of the other approaches.

un1k · August 11, 2018, 5:21pm

I had a look to my PaperUI and my default persitence is mysql. I found other threads concerning the circumstance that only with influxdb you get valid values. I get a zero back so I think in my rule the refernce to the influxdb is missing and the variable gets filled from mysql persistence. Is this a possible answer? I turned over to influxdb as default persistence in PaperUI but nothing changed.

val minsOnLeonieNow = actuator_office_2_ch2.sumSince(now.minusMinutes(5), "influxdb")

rlkoshak · August 11, 2018, 5:36pm

No because in your rule above you are passing “InfluxDB” to the calls to sumSince which will cause it to query InfluxDB regardless of what your default is. And I believe it returns null if you tried to use “MySQL” instead, rather than zero.

As far as I can tell you are doing everything right and the method is just not working right. There may be something I’m not seeing wrong.

un1k · August 12, 2018, 6:33am

please can you give me an example how to fill the states of the items “TV_ON_Time” and “TV_Runtime_Millis”. at startup for your third solution. I set them up in the .items file as Number. Do you have an example to solve the initial fillment wit the REST Api?

un1k · August 12, 2018, 1:50pm

I have a problem with these two lines. The Item TV_ON_Time is from Type DateTime I think and the Item TV_Runtime_Millis from type Number.

I initialized these two items in my rule:

rule "init tv"
  2 when
  3   System started
  4 then
  5   postUpdate(TV_ON_Time,new DateTimeType())
  6   postUpdate(TV_Runtime_Millis,0)
  7 end

My persistence policy is set to save on everyChange and to Restore on startup.
In the logfile you can finde the following error:

2018-08-12 15:39:38.825 [ERROR] [ntime.internal.engine.RuleEngineImpl] - Rule 'Calculate TV runtime': Could not cast 2018-08-12T15:39:35.490+0200 to org.joda.time.DateTime; line 16, column 23, length 28

I initialized the DateTime item with the actual Time.

rlkoshak · August 12, 2018, 4:17pm

When you know the item you are sending the command to, use the method instead of the action.

This might be over of those cases where the Action doesn’t work very well.

Where are the numbers coming from on the beginnings of the lines posted?

Is the postUpdate to TV_ON_Time line 16 in the file?

un1k · August 13, 2018, 7:19am

ok I post the whole rules script with initialization of the items as startup rule so that your can see all lines.

rule "init tv"
when
  System started
then
  TV_ON_Time.postUpdate(new DateTimeType())
  TV_Runtime_Millis.postUpdate(0)
end
  
rule "Calculate TV runtime"
when
    Item actuator_office_2_ch2 changed
then
    if(previousState == NULL) return; // ignore changes from NULL

    if(actuator_office_2_ch2.state == OFF) {
        val lastOn = (TV_ON_Time.state as DateTime).getZonedDateTime.toEpochSecond * 1000
        val totalRuntime = TV_Runtime_Millis.state as Number + (now.millis - lastOn)
        TV_Runtime_Millis.postUpdate(TV_Runtime_Millis.state as Number + (now.millis - lastOn))
        logInfo ( "TV_RULE", "complete result: " + TV_Runtime_Millis)
        //if(totalRuntime > 1000*60*60*10) { // 10 hours in millis
            // alert
        //}
    }
    else {
        TV_ON_Time.postUpdate(new DateTimeType)
    }
end

//rule "Reset TV runtime"
//when
//    Time cron "..." // use the Quarts cron builder to have this run at midnight every Sunday
//then
//    TV_Runtime_Millis.postUpdate(0)
//    if(TV.state == ON) TV_ON_Time.postUpdate(new DateTimeType)
//end

The log output:

2018-08-13 09:17:14.336 [ERROR] [ntime.internal.engine.RuleEngineImpl] - Rule 'Calculate TV runtime': Could not cast 2018-08-13T09:16:58.714+0200 to org.joda.time.DateTime; line 16, column 23, length 28

Line 16 in my rule is:

val lastOn = (TV_ON_Time.state as DateTime).getZonedDateTime.toEpochSecond * 1000

I used the method now in my startup rule to put initial values to my items.

5iver · August 13, 2018, 8:46am

If you were using VS Code, it would tell you ‘The method or field getZonedDateTime is undefined for the type DateTime’. getZonedDateTime is a method of DateTimeType.

val lastOn = (TV_ON_Time.state as DateTimeType).getZonedDateTime.toEpochSecond * 1000