Using InfluxDB difference function

1
votes

I have some measurement data in my influxdb database which I can query with:

select * from E_real_con
name: E_real_con
time value
---- -----
1537920001044785525 | 57160036.00
1538006401069651036 | 57227208.00
1538092800108297103 | 57294112.00
1538179200697333731 | 57366108.00

However, "value" is a cumulative value and I would like to get the delta/difference between two consecutive values.

I tried the following:

SELECT difference(last(value)) FROM E_real_con WHERE time >= now() - 7d GROUP BY time(1d) fill(null)

However, I get the following error message:

ERR: unsupported difference iterator type: *query.stringInterruptIterator

I would be happy to get some hints and feedback how to solve my issue.

I am using influxdb 1.6.1

Thanks a lot! Christoph

2
influxdbinfluxql
In the meantime I figured out, that the field "value" was of type "string". I used the command "SHOW FIELD KEYS FROM E_real_con" to show the field types for anyone interested. I deleted the old values and now "value" is of type "float". So the error message "ERR: unsupported difference iterator type: *query.stringInterruptIterator" is gone but there are still no results. – cmolitor

2 Answers

4
votes

I found the solution. The following two mistakes had to be corrected:

1) The values in the measurement were of type "string" and not "float". As the data was coming from nodered, I cleared the database and used parseFloat() in nodered before writing the data to influxdb. BTW: you can check the datatype of your measurement field by:

SHOW FIELD KEYS FROM E_real_con

2) It seems that the query command requires a "where"

This works:

SELECT difference(last(value)) FROM E_real_del WHERE time >= now() - 7d GROUP BY time(1d)

whereas:

SELECT difference(last(value)) FROM E_real_del GROUP BY time(1d)

does not work.

I hope this might help someone else.

0
votes

2) It seems that the query command requires a "where"

It's even more restricted than that. It requires a where with a minimum timestamp.

For example, the following gives me no result:

select difference(last(value)) from WaterConsumption_Total where time < now() - 1d group by time(1d) fill(previous)

While this does:

select difference(last(value)) from WaterConsumption_Total where time > '2019-08-23T00:00:00Z' group by time(1d) fill(previous)

This effectively makes it impossible to use such a query as a continuous query.