Hi
I’m writing Energy readings to an influxdb datasource and i’d like to do check/search/lookup for some values that will have been posted, but I can’t seem to get other query commands to work.
FYI - The server config is 192.168.1.222:8086/energy and I can confirm the following works
select * from Watts;
However these others attempted to search for the largest value ever written, plus many more don’t. what am I missing ?
select MAX "value" FROM "Watts";
select MAX * FROM Watts;
I’ve tried to work things out from influx, (below), but no joy there either..
https://docs.influxdata.com/influxdb/v1.7/query_language/functions/#max
My guess is that you are use to sql syntax...right? (me too, I've just started using influx)
try select max(value) from watts
no quotes
1 Like
I strongly recommend installing Grafana which will give you a graphical query interface. You can then see the actual queries it produces and you can then use them for yourself.
Hi @TotallyInformation
I already do have Grafana installed, but I’m still getting to grips with its query interface, and the Node Red approach for something quite basic seemed like it would be easier (at least that’s what I thought )
Thanks @zenofmud
This query does not give me an error but it only returns an [Empty] response ? No value is shown ?
Yes, the downside of InfluxDB is that its query language is close to but not the same as SQL.
You need:
SELECT max("value") FROM "watts"
Note also that the FROM clause may need prefixing with a database name and/or a retention policy name.
If that isn't working, we would need to see more about the structure of your database.
Hmm now I have learned something but I'm not suer what. In the cli fro influx I thought using quotes didn't work. I have to go try again.
In the cli I'd use show measurments
to see the rows names.
interesting, you can use or not use the quotes;
> select max("value") from "test"
name: test
time max
---- ---
1558443386066743407 9.968976471022282
> select max(value) from test
name: test
time max
---- ---
1558443386066743407 9.968976471022282
>
whats the output of:
select * from watts limit 5
Yes, the quotes are required for names with spaces or other odd characters. Grafana puts them in for you anyway.
1 Like
FYI - I have a very simple set up between node red and influxdb - just energy reading being sent. Appliance 0 is the energy monitor and the watts query is where I’m trying to pull values from
I’m looking to query my energy data and pull the following
The highest value ever
The lowest value ever
The mean value ever
Then , rather than pulling from ‘everything ’ - report the same thing again but by....
... the last hour
... the last 12 hours
... today
... last week
... last month
Here’s what the wildcard query returns .
select * from Watts;
No idea if this is even possible but wanted to give it a try ..
This also returns an [Empty] response ?
Case sensitive !!
Sorry, I should have checked the examples being shared
This one worked..
select max(value) from Watts
With the above in mind, I now have some good working examples now..
Thanks all for your help, I’ve been looking at the influx query language (InfluxQL functions | InfluxDB OSS v1 Documentation) and my next challenge is to do the same query, but this time only during a specific time period.
So I tried a few things and this felt the most likely,
SELECT max(value) FROM Watts WHERE time >= '2019–05-23' and time <= '2019-05-24'
But sadly not, it returns the following error.
"Error: Error from InfluxDB: invalid operation: time and *influxql.StringLiteral are not compatible"
Has anyone successfully done a query specifying a day range ?
my guess is you have to use the full date/time like
WHERE time >= '2019-05-23T00:00:00Z' AND time <= '2019-05-24T00:00:00Z'
1 Like
Many Thanks @zenofmud. that did it,
FYI - I also got this one to work too - to show me the highest in the last hour.
SELECT max(value) FROM Watts WHERE time >= now()-60m
3 Likes