Search code examples
differencederivativeinfluxdb

Influxdb derivative with random time points

I have a some value with random time-separated points and I would like the difference between each row with it's previous row:

time                    name    value
1440972000000000000     Froid   328
1443736800000000000     Froid   332
1444687200000000000     Froid   333
1445900400000000000     Froid   335
1447110000000000000     Froid   336
1447974000000000000     Froid   337
1448578800000000000     Froid   337.9
1449097200000000000     Froid   338.4
1449702000000000000     Froid   338.7
1450825200000000000     Froid   339.8
1452985200000000000     Froid   341.4
1454194800000000000     Froid   342.4

I guess I should use a derivative function for that but I never have the correct unit or value.

Eg: SELECT derivative(value) FROM water where "name" = 'Froid'

name: water
-----------
time                    derivative
1443736800000000000     1.4467592592592592e-06
1444687200000000000     1.0521885521885521e-06
1445900400000000000     1.6485328058028355e-06
1447110000000000000     8.267195767195768e-07
1447974000000000000     1.1574074074074074e-06
1448578800000000000     1.4880952380952004e-06
1449097200000000000     9.645061728395062e-07
1449702000000000000     4.960317460317649e-07
1450825200000000000     9.793447293447496e-07
1452985200000000000     7.40740740740725e-07
1454194800000000000     8.267195767195768e-07

I would like something like:

1443736800000000000     4  # (332 - 328)
1444687200000000000     1  # (333 - 332)
1445900400000000000     2  # (335 - 333)
[...]

I have no clue what to do to have this result.

Solution

  • I think DERIVATIVE might not be for what you think it is. The DIFFERENCE might on the other hand be what you're looking for, but it seems that it's not implemented yet..

    You could probably however use continuous queries in combination with LAST to accomplish this until the DIFFERENCE gets implemented. However this is a little hacky and the interval of CQ execution will probably need to be the same as the rate at which your points are coming in.

    Another solution would be to simply fetch what you have in the DB and then do the calculation inside your app, kind of in-memory approach. If you don't have too many points that you fetch at once then it shouldn't really be an issue.