hi,
may someone give me a working rule for displaying the value of the last record in mysql database of this item if an other item is updated.
please also add the imports and item definitions in .items and .sitemap.
also i want to display the min-/max-/average-values for 4h+12h+24 in 9 items. how does this work?
i’m still trying to make it work for hours, now i am very disappointed …
i am using openhab 1.8.3
regards
fly
You need to create separate Items to store those values, a rule to update them when they change, and then put those new Items on your sitemap.
Number MyItem
DateTime MyItemLastUpdate
Number MyItemMin4
Number MyItemMax4
Number MyItemAvg4
import org.openhab.core.library.types.*
rule "MyItem updated"
when
Item MyItem received update
then
MyItemLastUpdate.sendCommand(new DateTimeType)
MyItemMin4.sendCommand(MyItem.minimumSince(now.minusHours(4))
MyItemMax4.sendCommand(MyItem.maximumSince(now.minusHours(4))
MyItemAvg4.sendCommand(MyItem.averageSince(now.minusHours(4))
end
Getting the values for 12 and 24 is more of the same.
i c&p’ed your code, it is not working … yes i changed the items to my needs.
Items
DateTime str_homebb01_tempds00_lastupdate
Number str_homebb01_tempds00_minimum04
Number str_homebb01_tempds00_minimum12
Number str_homebb01_tempds00_minimum24
Number str_homebb01_tempds00_maximum04
Number str_homebb01_tempds00_maximum12
Number str_homebb01_tempds00_maximum24
Number str_homebb01_tempds00_average04
Number str_homebb01_tempds00_average12
Number str_homebb01_tempds00_average24
Sitemap
Text item=str_homebb01_tempds00_minimum04
Text item=str_homebb01_tempds00_minimum12
Text item=str_homebb01_tempds00_minimum24
Text item=str_homebb01_tempds00_maximum04
Text item=str_homebb01_tempds00_maximum12
Text item=str_homebb01_tempds00_maximum24
Text item=str_homebb01_tempds00_average04
Text item=str_homebb01_tempds00_average12
Text item=str_homebb01_tempds00_average24
Text item=str_homebb01_tempds00_lastupdate
Rule
(1) import org.openhab.core.library.types.*
rule "MyItem updated"
when
Item num_homebb01_tempds00 received update
then
str_homebb01_tempds00_lastupdate.sendCommand(new DateTimeType)
(2) str_homebb01_tempds00_minimum04.sendCommand(**num_homebb01_tempds00.minimumSince(now.minusHours(4))**)
(3) str_homebb01_tempds00_maximum04.sendCommand(num_homebb01_tempds00.maximumSince(now.minusHours(4)))
(4) str_homebb01_tempds00_average04.**sendCommand**(num_homebb01_tempds00.averageSince(now.minusHours(4)))
end
Between the "* * " the designer marks red underlined:
What the hell is going on?
regards
Not completely surprising. I just typed it in on my phone. I’m sure there is a typo
Are you using OH 1.x or OH 2?
import org.openhab. core.library.types.DateTimeType
2 and 3. I forgot that minimumSince et al all return a HistoricItem, not a State. Change these calls by appending a .state as in minimumSince(now.minusHours(4)).state
.toString' as inaverageSince(now.minusHours(4)).toString`.As a general rule errors like 4 can almost always be solved by calling toString which eliminates the ambiguity. Another alternative in this case is to cast the value to either a Number or a Command like this: ...averageSince(now.MinusHours(4)) as Number)
The full explanation about the root cause of 4 will take us into the weeds of how object oriented programming works which I’ve posted elsewhere on this thread before. Suffice it to say that in this case a DecimalType is both a Number and a Command so the interpreter didn’t know which sendCommand method you really wanted to call.
i found a typo, you forgot the last brackets.
as i wrote before, i am using OH 1.8.3
edit:
now, i have no errors, but nothing is displayed …
this are the item definitions
Number str_homebb01_tempds00_minimum04 "Minimum letzte 4h [%s]"
Number str_homebb01_tempds00_minimum12 "Minimum letzte 12h [%s]"
Number str_homebb01_tempds00_minimum24 "Minimum letzte 24h [%s]"
Number str_homebb01_tempds00_maximum04 "Maximum letzte 4h [%s]"
Number str_homebb01_tempds00_maximum12 "Maximum letzte 12h [%s]"
Number str_homebb01_tempds00_maximum24 "Maximum letzte 24h [%s]"
Number str_homebb01_tempds00_average04 "Durchschnittswert 4h [%s]"
Number str_homebb01_tempds00_average12 "Durchschnittswert 12h [%s]"
Number str_homebb01_tempds00_average24 "Durchschnittswert 24h [%s]"
and this is a part of the sitemap. the graph is displayed, well.
Text item=num_homebb01_tempds00 valuecolor=[>=40="#ff0000", >=37="#ff6e00", >=34="#ffec00", >=33="#b4ff00", >=30="#2cff00", >=25="#00e501", >=20="#009602", >=10="#009602", >=0="#00209f", <=0="#0000ff"]
{
Frame label="Errechnete Werte"
{
Text item=str_homebb01_tempds00_minimum04
Text item=str_homebb01_tempds00_minimum12
Text item=str_homebb01_tempds00_minimum24
Text item=str_homebb01_tempds00_maximum04
Text item=str_homebb01_tempds00_maximum12
Text item=str_homebb01_tempds00_maximum24
Text item=str_homebb01_tempds00_average04
Text item=str_homebb01_tempds00_average12
Text item=str_homebb01_tempds00_average24
}
Frame label="Letzte Aktualisierung der Werte"
{
Text item=str_homebb01_tempds00_lastupdate
}
Frame label="Diagramm: Innentemperatur oben"
{
Chart item=num_homebb01_tempds00 service="mysql" period=24h refresh=300000 visibility=[selectChartIntervall==0, selectChartIntervall=="Uninitialized"]
}
}
to go right, i do not want the actual date, i do want the last saved timestamp out of the mysql table of this item.
the temperature item “num_homebb01_tempds00” is updated with the correct temperature.
if i do a sql statement with sum / count i do get the values i want to display in the items min/max/avg, so i think, the table (=data) is right.
i do not get any infos out of the openhab.log
regards
The - indicates the Items have not been initialized yet. None o your Items will become populated until the rule executes (i.e num_homebb01_tempds00 recieves an update). Are you sure the rule is executing? Add a logging statement to make sure.
You can’t get the timestamp out of the MySQL table. The best you can do is to save the timestamp whenever the Item is updated in another DateTime Item, which is what the lastUpdate Item is for. Assuming your persistence is configured to save every update, this will work fine. If you save everyMinute or something like that then this wont work and I’m not sure there is a solution inside of openHAB. You could write an external script that pushes that value from MySQL into openHAB via the REST API or Exec binding.
OT
did you know what this could be in my openhab.log?
2016-08-25 07:13:01.134 [ERROR] [.o.m.c.i.folder.FolderObserver] - An unexpected exception has occured
java.lang.NullPointerException: null
at org.openhab.core.persistence.internal.PersistenceManager.initialize(PersistenceManager.java:370) ~[na:na]
at org.openhab.core.persistence.internal.PersistenceManager.itemAdded(PersistenceManager.java:342) ~[na:na]
at org.openhab.core.persistence.internal.PersistenceManager.allItemsChanged(PersistenceManager.java:337) ~[na:na]
at org.openhab.core.internal.items.ItemRegistryImpl.allItemsChanged(ItemRegistryImpl.java:201) ~[na:na]
at org.openhab.model.item.internal.GenericItemProvider.modelChanged(GenericItemProvider.java:393) ~[na:na]
at org.openhab.model.core.internal.ModelRepositoryImpl.notifyListeners(ModelRepositoryImpl.java:159) ~[na:na]
at org.openhab.model.core.internal.ModelRepositoryImpl.addOrRefreshModel(ModelRepositoryImpl.java:100) ~[na:na]
at org.openhab.model.core.internal.folder.FolderObserver.checkFolder(FolderObserver.java:142) ~[na:na]
at org.openhab.model.core.internal.folder.FolderObserver.run(FolderObserver.java:99) ~[na:na]
hi,
i figured out, that my rules wasn’t fired. i also figured out, that the error message above could come from openhab itself, i thought it came from the designer. 
i restarted OH - and what should i say - the items were populated … now i have only to figure out how to limit to 4 decimals … is there also a way to say i.e. 14.5000° instead of 14.5° ?
is there a possibillity to change the look of the graph?
thanks a lot man!!!
Well, it is coming from Persistence so it could potentially point to why you were having troubles. Beyond that, it is a pretty generic and uninformative error so I can’t say what could be wrong without digging into the code.[quote=“FyingEaglE, post:8, topic:13522”]
i thought it came from the designer.
[/quote]
Everything you see in openhab.log is coming from openHAB itself. Designer has its own log though I can’t think of a single situation where you would ever need to look at it unless Designer isn’t working.
In your Item definition, use [%.4f°] instead of [%s].
No. If you don’t like the graphs you will have to look to some other software or service to generate them and show them in your sitemap using a webview.
ok, thanks a lot!
back to the graph, may i am able to change the look of the graph?
specially the time and values on the axis are very small, i couldn’t read them well on my phone (samsung s4).
in the browser they are readable but the graphs-line is very antialiased. i tried with different default sizes, but all with same or nearly same result.
is it possible to display values (text) as multiline on an item?
where do i found a good website for maximumSince & co?
As I said, there are no customization parameters for the charts in openHAB. If you want different graphs you will have to look to something else (i.e. not openHAB) to generate them.
graph: 
did you mean, that i create my own graph and display it via image or webview?
multiline: this is not what i want, i want to display a larger text (maybe 3 lines with newline at the end) in an item. but i do not want to use a webview.
am i right, that there is no function for an overall-average or -max or -min? only between an specific date?
an additional question, how can i calculate the span between two dates? maybe from 1st january to now?
Yes, this exactly.
Cannot be done.
That is correct. But you could supply:
MyItem.maximumSince(new DateTime(0))
This will return the maximum since midnight, January 1, 1970 which is essentially the same thing.
Or just make your call
MyItem.maximumSince(now.minusYears(25))
Lots and lots of different ways. Each more or less appropriate to given circumstances. Where are you getting these dates? It matters.
that is not cool …
as i figured out, it is also not possible to add items dynamically …
there is another additional question, is it possible to make different sitemaps depending on the user?
may i permitted or deny some trees of the sitemap?
(i.e. me and my wife display everything also the switches i.e. for the heating and our children only the information-“tree”)
that is a very good idea!
it is a kind of a crazy thought, i want to display the time left from a given date (maybe directly written in a rule). so i am possible to calc i.e. the span between now and my birthdate …
Correct but you can set the visibility of an Item based on the state of that or some other Item. I use this all over my sitemap. See the “visibility” documentation on the sitemap wiki page.
Yes and no. You can make different sitemaps per user but you cannot restrict certain user to only that sitemap. Once you have access to OH you have access to ALL sitemaps. So if they know the names of the other sitemaps they can access them. In the phone apps it will list all the available sitemaps. You would have to use a reverse proxy like ngnix and implement the authentication/authorization there.
OK, assuming that the from date is stored in a DateTime Item:
val long totalMsec = now.getMillis - (MyDateTime.state as DateTimeType).calendar.timeInMillis
val long sec = msec / (1000) % 60
val long min = msec / (1000*60) % 60
val long hours = msec / (1000*60*60) % 24
val long days = msec / (1000*60*60*24)
// Getting at months is really complicated so we will ignore
// Getting at years is also really complicated because of leap years so we will ignore
// Both are exercises left for the student
val StringBuilder str = new StringBuilder
if(days > 0) {
str.append(days)
str.append(":")
}
str.append(if(hours<9) "0"+hours else hours)
str.append(":")
str.append(if(min<9) "0"+min else min)
str.append(":")
str.append(if(sec<9) "0"+sec else sec)
logInfo("Time", "Time since MyDateTime is " + str.toString)
i will try it … but it is not the same i want … depending on some situations, adding some additional items would be more what i want …
it sounds very complicated … i couldn’t imagine that nobody needs a security function like this …
thanks, i’ll try it out, but it looks difficult, in some other languages you can give a function date one and date two and as a result is everything year, month, day, hour, minutes and seconds.
regards
So under normal situations those items are invisible and they only become visible in that situation. For example, on my sitemap I have a Switch that tells me if one of my computers is offline. It only appears on the sitemap if the computer is actually offline using the visibility element. In another situation I use the visibility element to switch between charts of the same thing only for different periods (e.g. between last hour, last day, last week, etc).
Whether or not it is needed, the capability is not implemented.
This isn’t one of those languages. But it really isn’t that complex. Its just some simple math. The “%” operation is the mod, or remainder operation.
thanks for your help, i’ll come back with more questions 