Blockly or script - how to get sum of item since now to date

htttito · March 14, 2022, 8:30pm

OK, maybe i should tell the complete story:
I want to calculate my energy use and my energy infeed, because Shelly 3EM don’t calculate the energy balanced.
Here is my blockly script to save the average of the item: use and infeed:

This script i run with crone-job every 1 hour.
And now i only want to get the sum of this saved items.

Since 1 Week, i start to store this data.
But for the future i want a rule that store that data like my energy vendor do, maybe from:
31.08.2021 to 31.08.2022.
So i can see every day what my energy use/infeed is (the bill for the year is…)

rossko57 · March 15, 2022, 12:49am

Yep.

Okay, so in a rule you calculate an hourly average and post it to an Item state.
You don’t seem to do anything special about storing that state, so I guess you rely on the default persistence.
That will be rrd4j, storing the state every minute or twenty seconds or whatever it does by default.

If you get the sum of all those records over a year, it is going to be a really big number. I don’t think that is what you want.

I think you will need to have another rule that uses a live power reading to increment an energy counter, i.e. make a kWh meter. Most people do that on an “every minute” basis to keep the maths simple.

Then you can get a old kWh reading for a point in time from the database, and see the difference to "now’ reading.

htttito · March 15, 2022, 6:55am

OK, thanks!
I also have a mysql persistence.
I can save the numbers every hour to this.
How can i tell blockly to take the jdbc persistence?

hafniumzinc · March 15, 2022, 7:01am

You can’t - Blockly doesn’t have that feature at the moment. You have two options:

Change to using ECMAscript or DSL instead of Blockly.
Change your default persistence to JDBC. Blockly will grab data from the default persistence.

htttito · March 15, 2022, 7:16am

OK,

I changed my default persistence to JDBC.
I insert in persistence service to save the number every hour.
I can copy the code from blockly to ECMAscript.
Must I insert the “JDBC” to the code?
If Yes, where? (I am not really a script expert)

var dtf = Java.type("java.time.format.DateTimeFormatter");
        var zdt = Java.type("java.time.ZonedDateTime");
        function getZonedDateTime (datetime) {
          return zdt.parse(datetime + ' 00:00:00 +00:00', dtf.ofPattern('yyyy-MM-dd HH:mm:ss z'))}
        var persistence = Java.type('org.openhab.core.persistence.extensions.PersistenceExtensions');
        events.postUpdate('Gesverbr1', (persistence.sumSince(itemRegistry.getItem('Verbr'), getZonedDateTime('2022-01-01'))));
        events.postUpdate('Gesamteinspeisung', (persistence.sumSince(itemRegistry.getItem('Einsp'), getZonedDateTime('2022-01-01'))));

hafniumzinc · March 15, 2022, 7:48am

No, not if it’s now set as default.

Note that my previous post gave two options - you could do one OR the other. You don’t have to do both.

Is that with the Blockly code from this post? It’s just the word “insert” - most people just update or command their Item, and the persistence service will automatically grab that new value and store it. I’m not sure many people actively command the persistence service to store a value. It can be done, but certainly not with Blockly.

Just want to make sure there’s no confusion here, and also make sure that you know (or have tested) whether or not data is actually being stored.

Do you have a jdbc.persist file? Can we see it?

htttito · March 15, 2022, 8:28am

Do I understand right:

My Favorit:

If I don’t change my default persistence to JDBC, I must insert a Code into my rule with JDBC.
(Can you tell me where in the code i must write down the “JDBC”?)

Not so good:

If I change the default persistence to JDBC, I don’t must insert the JDBC code.

Here is my jdbc.persist file:

Strategies {
    everyMinute : "0 * * * * ?"
    everyHour   : "0 0 * * * ?"
   	everyDay    : "0 0 0 * * ?"
   	default = everyChange
}
 
Items {
    Shelly3EMSHEM3_KumulierterVerbrauch : strategy = everyMinute, everyDay, restoreOnStartup
    Shelly3EMSHEM3_KumulierterGesamtverbrauch : strategy = everyMinute, everyDay, restoreOnStartup
    Shelly3EMSHEM3_Leistung1 : strategy = everyMinute, everyDay, restoreOnStartup
    Shelly3EMSHEM3_Leistung2 : strategy = everyMinute, everyDay, restoreOnStartup
    Shelly3EMSHEM3_Leistung3 : strategy = everyMinute, everyDay, restoreOnStartup
    Shelly3EMSHEM3_KumulierteEinspeisung : strategy = everyMinute, everyDay, restoreOnStartup    
 
    ShellyHeizstab1_Temp1 : strategy = everyMinute, everyDay, restoreOnStartup
    Shelly_Temperatur_HW_Puffer : strategy = everyMinute, everyDay, restoreOnStartup
    Shelly1_Temperatur_HW_RL : strategy = everyMinute, everyDay, restoreOnStartup

    Sma_PowerL1 : strategy = everyMinute, everyDay, restoreOnStartup
    Sma_PowerL2 : strategy = everyMinute, everyDay, restoreOnStartup
    Sma_PowerL3 : strategy = everyMinute, everyDay, restoreOnStartup
    Sma_ActivePowerWatt : strategy = everyMinute, everyDay, restoreOnStartup
    Sma_DaylyYield : strategy = everyMinute, everyDay, restoreOnStartup
    Sma_TotalYield : strategy = everyMinute, everyDay, restoreOnStartup
    
    Verbr : strategy = everyHour, restoreOnStartup
    Einsp : strategy = everyHour, restoreOnStartup
    Gesamteinspeisung : strategy = everyHour, restoreOnStartup
    Gesverbr1 : strategy = everyHour, restoreOnStartup
}

Here is the rule:

var dtf = Java.type("java.time.format.DateTimeFormatter");
        var zdt = Java.type("java.time.ZonedDateTime");
        function getZonedDateTime (datetime) 
        {
        return zdt.parse(datetime + ' 00:00:00 +00:00', dtf.ofPattern('yyyy-MM-dd HH:mm:ss z'))
        }
        var persistence = Java.type('org.openhab.core.persistence.extensions.PersistenceExtensions');
        events.postUpdate('Gesverbr1', (persistence.sumSince(itemRegistry.getItem('Verbr'), getZonedDateTime('2022-01-01'))));
        events.postUpdate('Gesamteinspeisung', (persistence.sumSince(itemRegistry.getItem('Einsp'), getZonedDateTime('2022-01-01'))));

htttito · March 15, 2022, 1:27pm

OK the script doesn’t work.
When the item value change it state, there is all OK, but when the value is the same it writes the same value as the hour before.
But there must be a possibility to store or calculate with this values.
Openhab can show a graph with the right values and the right data table (for one day).
Here is the example:

and here is the data table to the graph:

JimT · March 15, 2022, 9:59pm

Here’s how to do it using JRuby. You can do this using GUI rule or define the triggers in a file based rule. If you are using openhab 3.2 you’ll need to add require 'openhab' at the top.

require 'openhab' # this line is not needed on openhab 3.3

start_date = Time.new(2022, 01, 01) # You could also use ZonedDateTime if you prefer
Gesverbr1.update Verbr.sum_since(start_date)
Gesamteinspeisung.update Einsp.sum_since(start_date)

This will use your default persistence service. If you have multiple persistence services installed and want to specify the one that isn’t the default or want to be sure regardless of what the default is, just add the service name at the end, e.g.

Verbr.sum_since(start_date, 'jdbc')

I noticed your persistence setting is everyChange. You might want to use everyUpdate for the relevant items?

You can also force the item to write to persistence by calling Gesverbr1.persist

Example:

require 'openhab' # this line is not needed on openhab 3.3

start_date = Time.new(2022, 01, 01) # You could also use ZonedDateTime if you prefer
Gesverbr1.update Verbr.sum_since(start_date)
Gesamteinspeisung.update Einsp.sum_since(start_date)

Gesverbr1.persist
Gesamteinspeisung.persist

htttito · March 16, 2022, 6:42am

Thank you! I will try it today, then I can give you a feedback

htttito · March 18, 2022, 10:57am

Now I am saving the values from my rule every hour in my JDBC persistence:

rule with blockly:
export

The values looks like:

Can anyone help me with the rule:
I want the sum of all “+” values from the database in an Item
and the sum of all “-” values from the database in an Item

rlkoshak · March 18, 2022, 1:48pm

You can’t do that with persistence. You’ll have to create a rule that does the sums as you go. The challenge will be to subtract each value after that time period has passed. I can think of two ways to do this.

Create a separate Item for the + values and the - values. Then have a rule that updates the + Item when the sensor value is positive and the - Item when sensor value is negative. Assuming these two new Items are saved in persistence you can then use sum since on each of these two Items to get the sum of just the positive and just the negative numbers.
Use timers and a rule that adds the value to the right Item (positive or negative) and then create a timer to subtract the value from the Item after the time period has passed.

1 is going to be more reliable and handle things like restarts and such better than 2.

htttito · March 18, 2022, 2:28pm

Hi Rich! Thanks!
I have the same idea.
But when I separate the items there is a problem:

When the value is 2 hours on the negative side:
…then it writes always the same value in the positive database.

Example:
In 3 hours the value is:
“+” 200
“-” 200
“-” 100

Then the “-” item is correct:
“-” 200
“-” 100

but the “+” item is wrong:
+200
+200

I want to write “0” in the value, but this want work, I don’t know why:
The rule writes the values perfect, but without “0”…

Here is my rule:

rlkoshak · March 18, 2022, 2:35pm

If you are using rrd4j it’s going to add an entry periodically whether the Item changes or is updated or not.

If you are not using rrd4j, the .persist file is probably telling OH to save the value every hour as it’s strategy.

htttito · March 18, 2022, 2:38pm

I use JDBC as primary persistence and save the values every hour.

rlkoshak · March 18, 2022, 2:39pm

Well, if you don’t want that for these Items you need to change the strategy so it doesn’t save every hour.

htttito · March 18, 2022, 2:46pm

I want to save it every hour.
But there is something wrong…

For example:
This are the right values:
18.03.2022 11:00 +200
18.03.2022 12:00 -500
18.03.2022 13:00 -450
18.03.2022 14:00 +500
18.03.2022 15:00 -300

The negative item shows:
18.03.2022 12:00 -500
18.03.2022 13:00 -450
18.03.2022 14:00 -450 instead of “0”
18.03.2022 15:00 -300

The positive item shows:
18.03.2022 11:00 +200
18.03.2022 12:00 +200 instead of “0”
18.03.2022 13:00 +200 instead of “0”
18.03.2022 14:00 +500
18.03.2022 15:00 +500 instead of “0”

rlkoshak · March 18, 2022, 2:58pm

There is nothing wrong. OH is operating as designed and documented.

You cannot have it both ways. If you want to save it every hour then you will have an entry every hour. That entry will always be the Item’s current state. OH always saves the Item’s current state. It doesn’t care if the Item hasn’t changed state in the last hour or not.

You really don’t need those zero entries at all. So change the strategy on these two Items to only save on Item updates instead of every hour.

htttito · March 18, 2022, 3:02pm

ON EVERY CHANGE…
This can be the solution
I check this out and give a feedback !

htttito · March 28, 2022, 6:01pm

Thank you all!
I have found the SOLUTION.
It is a little complicate to write, but now I can calculate the energy balanced
I compared it with the network supplier, there are only +/- 0,1-0,2KWh per day difference.