SQL to get change rate

InfluxDB 3
1

i have a measurement that it’s a counter (always increasing) and instead of returning the actual value of the counter i want to get the change rate. i can’t do this at graph level, i need to do this at backend SQL-query level. what SQL query can i use to get the change rate?

2

Hello @vvilaplana,
To get the derivative or rate you can do something like:

SELECT time, (co_delta_v / delta_t_ns) * 1000000000 as co_rate, (temperature_delta_v / delta_t_ns) * 1000000000 as temperature_rate FROM
(SELECT (lag(co, 1) OVER (ORDER BY time))  - co  as co_delta_v,
  	      (lag(temperature, 1) OVER (ORDER BY time))  - temperature  as temperature_delta_v,
             (lag(cast(time as bigint), 1) OVER (ORDER BY time)) - cast (time as bigint) as delta_t_ns,
                time
FROM airSensors WHERE sensor_id='TML0100' AND time >= ('2023-02-01 18:35:31.000')::TIMESTAMP
)
1 Like
4

i can’t make it work. i have this influxql query working:

SELECT non_negative_derivative(mean("AccessRequests")) FROM "freeradius" WHERE $timeFilter GROUP BY time($__interval) fill(null)

but i can’t make it work with sql. any clue? is it possible to do it without subqueries? non_negative_derivative() and non_negative_difference() are very useful. can’t they be implemented in influxdb3 sql?