CODEWITHSUNDEEP
selectnullnaninfluxdb
Rafał Kot
Rafał Kot

Reputation: 1084

Influxdb how to select and delete data with not integer value

I have pressure graph in Grafana like this:Pressure graph

There is one point with 'NaN' value. How can i delete it from InfluxDB? I tried this query:

> select * from pressure where value is not null;
> select * from pressure where value is null;
ERR: error parsing query: found NOT, expected SELECT, DELETE, SHOW, CREATE,       DROP, GRANT, REVOKE, ALTER, SET at line 1, char 39
> select * from pressure where value < 900;
> select * from pressure where value = 'NaN';
> select * from pressure where value = null;
> select * from pressure where value = -28;
> select * from pressure where value = "0";
> select * from pressure where value = '0';
> select * from pressure where value = 0;

But its not working ;/

I got this:

> SELECT * FROM pressure WHERE time > '2016-01-01T01:00:00Z'   AND time < '2016-01-01T04:00:00Z'
 name: pressure
 --------------
 time           database    value
 [...]
 1451611512766000000    home        1003.4
 1451611572834000000    home        -28.4

then:

 > insert pressure,time=1451611572834000000 value=1003.3
 ERR: write failed: field type conflict: input field "value" on measurement "pressure" is type float64, already exists as type string
 > insert pressure,time=1451611572834000000 value="1003.3"
 > SELECT * FROM pressure WHERE time > '2016-01-01T01:00:00Z' AND time < '2016- 01-01T04:00:00Z'
 name: pressure
 --------------
 time           database    value
 1451610011067000000    home        1003.6
 1451611572834000000    home        -28.4

Upvotes: 2

Views: 2236

Answers (1)

beckettsean
beckettsean

Reputation: 1846

This looks like InfluxDB 0.9. There is no good way to delete a particular point. That's one of the core assumptions to allow for the desired performance. NaN is also not a valid data type, but unfortunately there were briefly some ingestion paths that did not sanitize NaN entries.

Your best way forward is to determine the point with a bounded select, like SELECT * FROM pressure WHERE time > '2016-01-01T02:00:00Z' AND time < '2016-01-01T03:00:00Z' GROUP BY *. The NaN point should be somewhere in the return.

Once you find the point, record the full tag set and the timestamp. The tag set will be in the grouping of the query return. You can then submit a new point with the exact same timestamp and tag set, but with a different value for the NaN field. That new point will silently overwrite the old point.

Upvotes: 1

Related Questions