Need help with an Influx 1.8 query / find tags where |max-min| > 20 and return the tag + |max-min|

InfluxDB 1 InfluxQL
, ,
1

Using Influx 1.8. I have a bunch of devices that report temperature. Each device has a unique ID that is stored as a tag. I have the following query that identifies all of the IDs where the temperature varies by more than 20C within a 24 hour period:

SELECT “ID”
FROM (
SELECT max(“temperature”) - min(“temperature”) AS “temp_diff”
FROM “sys_data”
WHERE time >= ‘2023-10-01T00:00:00Z’ AND time <= ‘2023-10-05T23:59:59Z’
GROUP BY “ID”, time(24h, 4h)
)
WHERE “temp_diff” > 20
GROUP BY “ID”;

This works well enough and returns a list of IDs where the temperature difference is > 20C. However, what I would like is to return the value of “temp_diff” as well as the ID. i.e. get a list of IDs and the corresponding value of maxTemp-minTemp for each ID.

It seems as though it should be simple, but I can’t get anything to work. All suggestions welcome!

2

Hello @NickN,
My influxql is a little rusty tbh but I think you can do:

SELECT “ID”, "temp_diff"
FROM (
SELECT max(“temperature”) - min(“temperature”) AS “temp_diff”
FROM “sys_data”
WHERE time >= ‘2023-10-01T00:00:00Z’ AND time <= ‘2023-10-05T23:59:59Z’
GROUP BY “ID”, time(24h, 4h)
)
WHERE “temp_diff” > 20
GROUP BY “ID”;
3

That worked! Thank you!