SQL query result in rules?

Hi all

I’ve started implementing an irrigation system. I’ve got two circuits and would like to keep track of how many minutes each one has already been active today. I found similar threads (Generator hours counter) but I have a feeling that this should be easier.

Since I’ve got persistence with MySQL setup, can I execute specific queries and use these in rules?
In my case, I log the state of two switches (for the two circruits) every minute. So I basically just need to count how many entries I have in the database with the state “ON” for today. I see there are a few methods like AverageSince or MaximumSince. I would need something like “SumIf” or the ability to execute any aggregate sql.

Any ideas?


There is sumSince which will give the sum of the items since the passed in datetime. The full set of methods on an Item can be discovered by pressing ctrl-space in Designer and are documented here.

However, these methods are the only ones available for querying data out of the DB.

1 Like

That sounds promising. But strangely, that method is not listed when I press Ctrl-Space. Was going through that list twice yesterday and didn’t find anything suitable, that’s why i asked.

Here’s a screenshot of my editor (tried the editor in Windows and Linux, same result):

The item I’m trying this on, is a switch. Could that be the problem? In the database I have ON / OFF, I assume I would need 1 and 0 to execute a sumSince.

That will be a problem. You need it to store 1 and 0, not ON and OFF for sumSince or any of the other persistence arithmetic methods to work. You will either have to use an alternative DB that does store Switches as 1 and 0 or use something like the link you have in the original post.

Mhh… OK. But still: why can’t I see sumSince in the list of available methods?
Tried to create a number item and don’t get the method either.

I can’t answer that. If might be one of the many problems with Designer right now. Version 0.8 of ESH Designer has lots of known problems and 0.9 is unusable so despite its flaws it is all we got right now.