Hi!
Thank you for answer!
Not working is: From now to “- 1” year is not to the date i want.
Or I don’t understand the blockly script correct.
When today is the 14.03.2022 and 1 year back it is the 14.03.2021 but i only want to go back to a exact date.
OR:
Is there a simple possibility to get the sum of all numbers, without: now, date, or something?
Maybe try:
The “now” block attached to the persistence block is a default shadow block. You can put another one over the top of it.
I tried, but there came no result…
Where came no result?
Try simplifying your script to just print the number to the log:
In my log I then get:
20:18:15.590 [ERROR] [org.openhab.rule.c792f80087 ] - 18020.0
OK, what’s the nature of the date? Is it in an Item, hard coded, etc?
Try installing Date and Time which needs to be separately installed from Settings → Automation. That will
Errors in the logs? That should have worked. Looking at the code it generates a ZonedDateTime with that date and midnight as the time.
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');
persistence.averageSince(itemRegistry.getItem('MyItem'), getZonedDateTime('2022-03-14'));
Install the Date and Time library and use those in place of the now block. That will give you more flexibility if you need it.
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:
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…)
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.
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?
You can’t - Blockly doesn’t have that feature at the moment. You have two options:
OK,
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'))));
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?
Do I understand right:
My Favorit:
Not so good:
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'))));
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:
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
Thank you! I will try it today, then I can give you a feedback 
Now I am saving the values from my rule every hour in my JDBC persistence:
rule with blockly:
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
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.
Hi Rich! Thanks!
I have the same idea.
But when I separate the items there is a problem:
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:
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.
I use JDBC as primary persistence and save the values every hour.
