Influx - where "value" > x did not get results

[pihlo]

I started with influx. I install (1.6.4) on a Raspi4. I use the influxdb out node and link them to 3 weathernodes. At the console of influx I see the 3 measurements.
One of them is "TempAussen" [German for Temperature Outdoor].

> select * from TempAussen
name: TempAussen
time                value
----                -----
1613497536173210227 5.30
1613497935692276610 5.30
1613498125463913644 4.80
1613498242269076905 4.80
1613498279334013322 4.80

I tryed now to select by using a where clause (I am absolut familar with SQL)
But: select * from TempAussen where [value | "value"] > [5 | "5"] gives no result.
[A|B] means I tried multiple values - like with A and also with B
Also max(value) group by something did not get any result.

I read that this is a "common" problem for beginner - but I did not found "good" explanation.
I use SUFU, Google, ....

I am sure that when I read "the" solution i cry because it would only be a small change.... But - what is "The" solution?

[TotallyInformation]

SELECT * FROM "TempAussen" WHERE "value" > 5.0

Should work.

The easiest way to experiment with InfluxDB queries is to set up and use Grafana.

[Colin]

Are the values you are writing strings or numbers? I suspect they are strings.

[Colin]

Run
show field keys from TempAussen
and see what it says.

[pihlo]

@TotallyInformation

SELECT * FROM "TempAussen" WHERE "value" > 5.0

No, didnt work - I also tried.
And @Colin - yes this are strings. outomatic created when I connect the Out Node.
And I tried also to compare with > "0" and also with >"." which if the system did not made a casting should give me also the full list

Using database myhome
> SELECT * FROM "TempAussen" WHERE "value" > 5.0
> SELECT * FROM "TempAussen" WHERE "value" > 0
> SELECT * FROM "TempAussen" WHERE "value" > "0"
> SELECT * FROM "TempAussen" WHERE "value" > "."
> show field keys from TempAussen
name: TempAussen
fieldKey fieldType
-------- ---------
value    string

@TotallyInformation

The easiest way to experiment with InfluxDB queries is to set up and use Grafana

Yes - maybe - but I do not want to use Grafana [yet]. First I want to understood the basics - and this where condition is a base basic...

[Colin]

You can't do numeric comparison on strings. You will have to change the data to be numeric type, to do that make sure the value you send to the influx node is a number not a string. I think you will have to drop the measurement and start again.

If you don't know how to get the value as a number in node-red then show us where it is coming from.

[TotallyInformation]

@pihlo

Yes - maybe - but I do not want to use Grafana [yet]. First I want to understood the basics - and this where condition is a base basic...

Grafana will help you get to understand the basics of queries as it lets you do point and click queries that you can then see in raw format.

General rules for InfluxDB:

Fields (value is the default field name) should always be numeric (take care between integer and float).

Tags should hold textual data (metadata) for fields. For example, if you had multiple sources for external temperatures, you would add a "location" tag to your table.

[pihlo]

I agree with all the advices and hints. Yes - value should be numeric because it is nummeric.
But I tried the simple input node which then generate and fill up the mesarument (a simple MQTT message is behind).
Grafana - yes ... but not yet...

And - the main problem still exist: The value "4.50" is a string, the where condition with > "0" should show this - or not? I havent read that I can use where only with (type)float, Integer....

[Colin]

Feed it through a function node containing

msg.payload  = Number(msg.payload)
return msg

that will convert it to a number. Then pass it to the Influx node.

[pihlo]

OK, I delete all - create a Subflow and deploy.
Works well. Thanks for support.
Lession learned: Influx Where Condion only works with Numbers / Time (which made sence because the main aerea for this DB are timeseries with time/number pairs

[Colin]

I expect you can use Where with strings if you are looking for equality, or not equal, though I have never tried it. The real lesson is that if you want to put numeric values into influx you should make sure they are passed to influx as numbers not strings.

I don't think that Grafana would have been able to chart them either when they were strings.

[Edit] I suspect most of us have fallen over this issue at some point, it is easy to forget to convert to numbers if they are not already.

[Colin]

@Colin

Feed it through a function node containing ...

In fact that is not necessary. It should be sufficient to select, in the MQTT node Output field parsed JSON Object which tells the node to expect a JSON string, parse it and convert it to a javascript object. When it gets just a string containing a number then that will convert it to a number.

[pihlo]

PERFECT! Of course - sometimes the simple things are forgotton in a bunch of "cool stuff".

[TotallyInformation]

@Colin

I don't think that Grafana would have been able to chart them either when they were strings.

No it can't - hence the advice I've given. Fields must be numeric. Even boolean doesn't work well. Use 1 and 0.

@Colin

I suspect most of us have fallen over this issue at some point

Yup, and worse.