Mysql persistence get time for last inserted value

Add-ons Persistence Services
1

Hello,

i’m using mysql for persistence and motion sensor that inputs values CLOSED and OPEN in database.
Now i need to get the time for last inserted CLOSED value and calculate time difference from now and that record to get
last motion detected?
Is that even possible?
Any suggestions?

KR, Tomaž.

2

Well, I don’t think there is a way to directly query the db for such values withing OH. However, you could easily create a rule for your motion sensor item when updated from OPEN to CLOSED. In there you can store the current timestamp in another item.

Contact motionSensor
DateTime motionSensorLastClosedDate

rule "last closed time"
when
    Item motionSensor changed from OPEN to CLOSED
then
   postUpdate(motionSensorLastClosedDate, new DateTimeType())
end

rule "last detected motion"
when
    Item motionSensor changed from CLOSED to OPEN
then
   // calculate the difference between now and motionSensorLastCLosedDate value
  // do something with it
end

Now the above probably won’t work out of the box. I’m new to OH so there might be better ways, but this is what I would do at this point.

3

Ok, tnx,
i will try that way and see how it working.

kr, tomaz.

4

@vertom have a look at this post. It is working fine for my installation. If you have more then one persistence, you have to identify it with a second parameter:

... door.changedSince(now.minusSeconds(1), "mysql")

5

Or see here How to assign lastUpdate to an item?

6

Thank you guys for your replays…i didn’t try it none of them, i have a little rush in my life and also my computer get broken,

i will definitely response it how did i managed it.
:slightly_smiling:

kr, tomaz.

7

Hi,

this is my probably final solution and there is the code

rule “Cron job every minute”
when
Time cron “0 0/1 * * * ?” // every minute
then

var Calendar cal = Calendar::getInstance()
cal.setTime(Motion.lastUpdate(“mysql”))

//poskusimo dobiti razliko vrednosti med časom!:slight_smile:
var DateTimeType time = new DateTimeType()

var int m = Minutes::minutesBetween(new DateTime(cal.timeInMillis), now).getMinutes()

var String s = String::format(“%1$tH:%1$tM / %1$te.%1$tm.%1$tY”, cal)

MotionSince.postUpdate(s + " (" + m + “min)ago”)

end

thank you for all suggestions!

kr Tomaz