Create time filter from query and more than basic WHERE

Hello :slight_smile:

I run Grafana (v8.5.2) and influxDB on a raspberry pi to monitor heating data. I’d like to estimate how much energy my solarthermal heating system produced. For that I got different measurements that contain different data points: measurement1 contains the pump_state (a boolean). measurement2 contains the storage_temperature.

First, I would like to get the derivative of the storage_temperature and sum that up. SELECT derivative("storage_temperature") FROM "measurement1" WHERE $timeFilter works and gives me something that looks like a derivative. However, SELECT sum(derivative("storage_temperature")) FROM "measurement1" WHERE $timeFilter does not return any data. Why? sum without derivative works…

Second, I’d like to filter for when the pump is being turned on. Since this is in a different measurement, I thought about a time filter that contains the periods from switch on to switch off and use this time filter for the storage_temp reguest. Therefore, I tried SELECT derivative("pump_state") FROM "measurment2" WHERE $timeFilter AND derivative("pump_state") > 0 to get the switch on times but that does not return any data. Setting the condition to AND "pump_state" > 0 does return data. Why?

Third, the storage is sometimes being used while the pump is on. To increase accuracy, I would like to only use the positive values of the derivative, but again: adding the condition AND derivative("storage_temperature") > 0 to the request results in no returned data.

Is there any way to achieve such calculations, or do I better use python with numpy to analyze the data?