Reading values of an item from database

Hi, i am running a MySQL database storing the values of my items. For the item in scope, it looks very simple and like that:

time                  value
2016-06-30 01:15:00 	1
2016-06-30 01:30:00 	0
2016-06-30 01:45:00 	1
2016-06-30 02:00:00 	0
2016-06-30 02:15:00 	1
2016-06-30 02:30:00 	1
2016-06-30 02:45:00 	0

value 1 indicates ON and value 0 indicates OFF.

my simple question, I would like to read out of the database from “when” until “when” the item was Switched on. This allows to have in sitemap:

item did run last time from 02:15 until 02:30.

Anyone an idea how I can do this in a simple way with OH1.8?

There is no simple way to do this.

You can keep track of the time in rules and update an Item while the switch remains ON. You can use the previousState(true) method on the Item to figure out when the Item most recently changed state and use that to update an Item.

There is no way to directly query the database from rules.


Hello Rich,
may be this post is out of date but for me still not being clear if now is possible to query database directly from rules.
For example, I have an influxdb database where I store the desired temperature of my house during the different seasons.
I have a rule for switching on an off my heating/cooling system, and in this rule I activate my system doing a comparison of the current temperature with a “temperature set point”. I would like to get this setpoint from the database depending of the season.

Does exist already a direct way to do that? If not I suppose the best solution is to implement some script to cast the variable value?

Thank you in advance!

The limited set of queries avalable in rules for use with persistence includes historicState() , which allows you fetch the state of an Item at a specific moment in time.
If there’s no data for that exact moment, the next-previous state is fetched (on the basis that it hadn’t changed since)

I don’t understand what you’re up to - fetching last year’s enviro data as a starting point for today?

Not all the persistence services support al the functions.

I´ve readen the post about persistence before but I was wondering if there is a better way. Because the information that I have in my database is not related with states of an Openhab´s Item.
In my database the information is like that:
Month Temperature Objetive
January 25ºC
February 24ºC

and so on…
This example it´s simple and someone could just say: “Why you dont write that directly in the rules”
But the problem is that I have this information according to each room, the kind of use of the room and the occupacy… That´s why i´m trying to have like a “fix code” in openhab which activate and desactivate the heating/cooling system depending of one variable which i´m getting from different database depending the room.

Thank you for your answer!

Then you cannot use openHABs persistence services with it.
There are no other built in methods to get arbitrary data from any database.
You’d have to script it.

1 Like

Thanks a lot for your answer. I will try it and share with the community.

What would be really great is if OH would offer some simple methods that allow to say:

  • Last time on / off
  • Last period of time on / off

If i would be developer, than i would program something like this, as the way via rule seems to be very complicated…