[SOLVED] Double value in MySQL not recognized as number in rule? Or is it something else?

Setup, Configuration and Use Scripts & Rules
1

I have this (very) weird thing happening here.

I literally have a clonning version of my server and in one server the rule works flawlessly for over 2 years now and on the cloned version, it does not!

This is surreal.

So the rule is as basic as this:

rule "Irrigation rule"
when
   Time cron "0 0 0,8,12,16 * * ? *"
then
   if (Humidity.historicState(now).state < 90) {
      Irrigation.sendCommand(ON)
      offTimer = createTimer(now.plusMinutes(2), [|
         Irrigation.sendCommand(OFF)
      ])
   }
end

This is it. If a certain number store in MySQL (in this case the humidity) is lower than 90, then it starts the irrigation for 2 minutes. As simple as that.

I’ve already put a lot of logInfo and it does show the number properly. There is no “comparison” error any other error. It simply does not go inside that “if” condition.

I even tried to “force” that number to be an integer and it did not worked as well.

I’m just stuck here.

Please help! :slight_smile:

2

Show us what you logged. There may be something non-obvious to you that would be meaningful to us.

Without that :person_shrugging:

You are not specifying mysql in the call to historicState. Are you sure it’s grabbing the value from MySQL? Is that your default?

1 Like
3

You are absolutely right, but I fear there isn’t much to see. :frowning:

So at the very begining I’ve put this:

logInfo("irrigation", "Before IF: " + OutsideHumidity.historicState(now).state)
if (OutsideHumidity.historicState(now).state < 90) {
   logInfo("irrigation", "After IF!")

And the only thing I get in the log is literaly this:

2022-05-25 01:48:54.172 [INFO ] [openhab.core.model.script.irrigation] - Before IF: 85

In MySQL, if I check the field is as double.

I mean, I don’t want to repeat it, but this is a clone of a perfectly working RPi. In the original it works!

I’m definitely stuck. :frowning:

4

I’d first question if your rule retrieves from MySQL at all.
Humidity.historicState(now)
will retrieve from whatever you have set as your system default persistence service.
In OH3, that will be rrd4j unless you have changed it.

You can be explicit in your rule - are you using via JDBC?
Humidity.historicState(now, "jdbc")

Next thing to consider is Item type. Is this a Number type? Or perhapsa Number:Dimensionless? Has it been changed from one to the other in the past, perhaps?
You can get some really nasty effects involving Number:Dimensionless types (usually expressed with a percent unit “%”) if it involves numbers without a unit - these get an invisible unit “ONE” and are treated as ratios.
Just as the quantity “50 %” is not equal to numeric “50”, neither is the quantity “50 ONE”, even though it formats to “50”.

5

Yes I am.

The item is indeed this, but it is stored in the database as a double. So as we can see on that logInfo, it shows exactly the number 85.

If I only output directly the item state (OutsideHumidity.state) it actually shows the % in front of the number, just as we see it in the item. But that is why I am grabbing the number that I have in my persistance.

What am I missing here?

6

The fact that it’s stored in the database as a double is irrelevant. All numbers are stored as a double.

Persistence does not yet support units of measurement. So what’s going on is exactly what @rossko57 described. The value is restored. There’s no unit provided so the default unit is chosen. The default unit for Number:Dimensionless is ratio which is the one unit that doesn’t actually have a visible unit. Exactly 85 in this context is exactly 85 ONE. OH doesn’t show the units for a ratio but that doesn’t mean it’s not there.

The flow is like this.

  1. The database is queried and it returns 85
  2. OH get’s that number and sees that the Item is a Number:Dimensionless meaning it’s a QuantityType. So it goes down that path instead of the path for plain old Numbers
  3. OH looks at the State Description metadata to see if you have configured the desired units to use. You haven’t.
  4. OH uses the default units based in the category (in this case Dimensionless) and your configured locale. In this case that’s ONE which doesn’t actually have a representation.

Unfortunately, OH does not have the ability to do math with QuantityTypes and non-QuantityTypes. Both operands need to be one or the other.

Humidity.historicState(now).state is a QuantityType. 90 is just a plain old number. So you either need to convert the QuantityType to a plain old number:

Humidity.historicState(now).state.floatValue

Or you need to convert the 90 to a QuantityType. I’m not actually sure how to do that for a ratio, maybe something like one of the following:

90|
new QuantityType(90, '')
90|ONE
new QuantityType(90, 'ONE')

One of those four options ought to work.

But wait, this is a Humidity Item. It’s not a ratio, it’s a percentage. So let’s make it behave like one.

  1. Make sure you’ve defined State Description on this Item and set the units to %.0f %%. Theoretically that should make OH use percentage instead of ratio as the units (see step 3 above).

  2. Convert the 90 to a percentage QuantityType using 90|%.

If that doesn’t work, we may need to force it in the rule.

if(Humidity.historicState(now).state.toUnit('%') < 90|%) {

NOTE: You may need to cast the state to a QuantityType for Rules DSL to see the right methods: e.g. (Humidity.historicState(now).state as QuantityType).toUnit('%').

1 Like
7

Thank you so much once again Rich. :slight_smile:

Yes, it is finally working and I didn’t knew, at all, these differences. I always assumed the number came straight from MySQL “as is” and not being transformed “in the background” by OH. This was very educative and it actually made light on other things that were happening on other rules. :slight_smile:

So, in case others want to see the final result, I ended up placing this before the rule:

val Number humidity = (OutsideHumidity.historicState(now).sate as QuantityType).toUnit('%')

Then I just had to do this immediately in the start of the rule:

if (humidity < 90|%) {

And that’s it! It’s working flawlessly. :smiley:

8

Just to be clear though. It’s not being transformed, per se.

historicState.state returns an org.openhab.core.types.State Object just like MyItem.state does. There are all sorts of different State types: QuantityType, OnOffType, PercentType, DateTimeType, etc.

The type of the state depends on the type of the Item. A Number:Dimensionless carries a QuantityType and no other (ignoring UnDefType which you’d never get back from persistence anyway). So when you want to get the state of an Item at some point in the past for a Number:Dimensionless, the only thing that makes sense is to get a QuantityType.

Note, I strongly recommend setting the State Description for this Item so that it’s treated as a percentage by everything in openHAB rather than keeping it as a ratio. If you already have, this might be a limitation with persistence right now and it’s worth taking note of this behavior.

9

By the way, have you noticed that instead of “state” I wrote “sate” by mistake? I mean, how can this be working if I got it wrong? Does it asume “0” in case there is no valid information?

Also, I’ve tried what you suggested, which actually makes total sense but I am just being very noob here. I’ve tried different combinations and I am not being able to make it work. Am I placing it in the right place here?

humidity
humidity799×809 65.5 KB

I don’t find any other place to set the “state” other than on the JSONPATH.

10

That’s the Link. That’s not the Item.

Go to the Item page. There will be an “add metadata” link you can click and choose “State Description” from the list.

Put %.0f %% in the Pattern field.

image
image1454×586 32 KB

Make sure to undo anything you’ve done on the link. That will probably break something.

It’s not working if it’s sate in the code itself that way. When it encounters that line it will complain something like “HistoricState does not have a sate property”.

1 Like
11

After this, should I change the item type to number only in order to be able to use it directly for comparison?

Also, of I want to send this to Google, what metadata should I setup and how?

Sorry for the new question, just trying to save starting a new post only for this. :slight_smile:

12

No. Just do the comparison with a quantity. (in DSL rules that’s like blah > 50 | % as you’ve already found)

1 Like
13

It just works and that’s it! :slight_smile:

Thank you guys, you are the best!

PS: What about that part to make it work in Google Home? :smiley:

14
1 Like
15

By the way, what if I want to apply this to temperature?

Let’s say, I use degree Celsius (symbol: °C), should I put it like so: blah > 50|“ÂșC”?

16

When units were first introduced, we had to put them as “strings” just as you show.
blah > 50|"ÂșC"
That still works.

Since then, a DSL enhancement allows ‘naked’ units after the pipe.
blah > 50|ÂșC
but beware - some units include characters that will upset the rules parser, the obvious one being / which the parser wants to use a maths operator.
bleh > 50|km/h error, parser wants to divide 50km by variable h
bleh > 50|"km/h" okay

Other rules languages have their own methods and limitations (the pipe | designator is unique to DSL)

1 Like
17

It’s weird because first I tried it naked just like that and the rules were not running, giving me this error in log:

2022-05-27 11:26:51.448 [WARN ] [el.core.internal.ModelRepositoryImpl] - Configuration model 'estores.rules' has errors, therefore ignoring it: [52,59]: no viable alternative at input 'Âș'
[52,60]: missing ')' at 'C'
[54,42]: mismatched input ')' expecting 'end'
18

It’s probably not the correct symbol °C (as usually used) looks a bit different from ÂșC (copied from your posts). I see a little line under it in my browser.

1 Like
19

You are right, it is indeed different. :slight_smile:

Let’s try with this one and see what happens.