Influxdb Query -

General
1

Hello
this is a query -

SELECT Tn20GT
from
(SELECT COUNT(average) as Tn20GT
FROM spez_days
WHERE average >=20
AND time > now()-12h)
where Tn20GT>1

basicly it should check if in the last 12h the temperatur (average) was about 20 degree!
if it is below 20 the following switch Node will drop it.
But it is not working properly

2

You have two “where” clauses

What is the schema for that database?

3

With complex queries like this the best plan is to work from the inside out. So first get your inner select working. If it doesn't immediately work then simplify it initially and then build it up.

I don't understand why you have the outer query. Why don't you just use the inner query and test that in the flow?

What has this go to do with node-red?

4

It seems to me that if there are no rows where the average is equal or above 20, the inner select will return nothing and the outer 'where' clause would cause nothing to be return ie 0 rows returned

5

Yes, I think the inner will return zero or a value and the outer will return 0 or 1 rows. So it is pointless using the outer select. Just use the inner and test for zero in node-red.

6

Hello @Colin @zenofmud
for your feedbacks, yes has nothing to do with node red. except it have this sql in a Node.

removing the outer select do not change it.

basicly if the last 12h the temperature was allways >= 20 degree it is OK, i one drops below this it is not OK.
If it is OK it will push another query to calculate the mean temperature over the last 12h and tell with a telegram.

have a nice day and Merci for your help
vinc

7

You have not shared the structure of your database table, but does this work?

Assuming that "average" is the name of the field holding your temperature (I don't think that is a good choice of field name!) :
SELECT MAX(average) as Tmax, AVG(average) as Tavg FROM spez_days WHERE time > now()-12h
It will return 1 row. (or zero if there is no data)

A Switch node to test if msg.payload[0].Tmax >= 20
Then send msg.payload[0].Tavg to Telegram

8

Show us the single query that does not work, and tell us what you mean by does not work. What does it show when you feed the result into a debug node?

9

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.