Hi everyone,
I am working on a weekly report about our house to raise awareness in the family about energy consumption and to have some fun weekly trivia to talk about.
I already managed to create tables with weekly consumption and cost for water and power and I am planning to integrate some weather data.
Now, an idea crossed my mind where I need some help from you. I don’t even know if it is possible at all. Anyway, it’s beyond my skills.
The idea is to create rankings for groups of items with the result of top 5 lists. For example: Which windows were opened or which lights were on the longest in the past week? Or who was at home the most?
I have my groups persisted with MariaDB. I guess I would need to create a rule which sends queries to the database for the items. Since MariaDB doesn’t store the group connection I guess I need to create a query with item numbers. Then I need to have OpenHAB items for top 1-5 windows for example and send the query result to window-open-top1, then window-open-top2 items and so on.
The next step would be to find out the duration for these items to show them in the list:
- Kitchen Window 2:30 hours
- Bathroom Window 2:10 hours
- Living Room Window 2:05 hours
- Bedroom Window 1:46 hours
- Guest Bathroom Window 1:30 hours
That’s the theory. Is this possible at all? Can anyone of you experts help me with this?
Thank you very much. Best, Max
I have no idea if it’s possible to build such a list (in a reasonable way) in openHAB directly, but I guess it would be possible to build a stored procedure in MariaDB, then build a script to query the stored procedure through exec and get the result as a csv list to an item.
Finally, it would be a simple rule to extract item names and values and set a bunch of string items to display the values.
1 Like
Thank you very much for your reply @Udo_Hartmann. I read in a post where you also participated that it is possible to run a sort query. Would this be a solution for my project? It sounds like this would give a chance to work with a query for an OH group.
This is the discussion I am referring to:
https://community.openhab.org/t/how-to-sort-a-list-of-values-and-names/133583
Hmm… Maybe an option. You would have to get the value of each item for the time span.
Then sort the list of values, grab the five biggest numbers, then check the corresponding names to the values. Something like this:
rule "build summary"
when
Time cron "00 0 0 ? * MON" // Monday, at 12:00:00 AM
then
gConsumption.members.forEach[i| // group of persisted items
val Number nMax = i.maximumSince(now.minusDays(7)).state as Number // get max of last 7 days
gCalcConsumption.members.filter[j|j.name.startsWith(i.name)].head.postUpdate(nMax) // write to corresponding item
]
end
rule "get top 5"
when
Time cron "30 0 0 ? * MON" // Monday, at 12:00:30 AM
then
gCalcConsumption.members.sortBy[ -(state as Number) ].forEach[i,j| // sort list of max values (descending)
if(j > 5) // if counter is above 5
return; // drop
logInfo("buildList","No: {} Name: {} Value: {}", j, i.name, i.state) // write a log with the Position, Name and actual Value
]
end
The first rule has to build the value (depends on what value to get). It’s called Monday at Midnight.
The second rule will sort the values. It’s essential to give openHAB some time for the first rule (depends on function and count of values).
Instead of logInfo() you have to build a string, containing the list. Maybe a stringBuilder would be a nice option. Be aware that you have to add carriage return and line feed for each line.
the rule depends on two groups, which have to have as many items as there are values (e.g. if there are six family members, there have to be 6 items in each group) and the name of the top-item has to start with the name of the first item, like that:
Item homeUdoTime -> time span of ON state for Item homeUdo. Each ON phase is persisted when it's over.
Item homeUdoTimeSum -> this item will get the sum of the past 7 days.
Please be aware that there is no direct way to get the time span for ON or OFF from a persisted item (afaik), so you will have to do some tricks for that. Maybe it’s possible to calculate the last last ON time span and store that value (but you have to ensure the value is persisted only once per ON time span):
rule "get last ON to OFF"
when
Item homeUdo changed to OFF
then
val start = homeUdo.previousState(true).getTimestamp.toInstant.toEpochMilli // get time of ON state
val theEnd = now.toInstant.toEpochMilli // get time of OFF state
val timeSpan = ((theEnd-start)/1000).intValue // get difference in seconds
homeUdoOnTime.postUpdate(timeSpan) // write to Item
homeUdoOnTime.persist // do a persist once
end
Thank you very much! I really appreciate your suggestions. I will try to adapt your ideas to my scenario and will report back whether it works.
