Can you delete data from influxdb?

Question

How do you delete data from influxdb?

The documentation shows it should be as simple as:

delete from foo where time < now() -1h

For some reason, influxdb rejects my delete statements saying "Delete queries can't have where clause that doesn't reference time"

select * from bootstrap where duration > 1000 and time > 14041409940s and time < now()

I want to delete these 5 entries whos duration > 1000 seconds

This should be a valid sql statement, yet it fails

enter image description here

None of these delete statements work either

delete from bootstrap where duration > 3000000"

delete from bootstrap where duration > 300000"

delete from bootstrap where time = 1404140994043"

delete from bootstrap where duration > 300000 and time > 1404141054508 "

delete from bootstrap where duration > 300000 and time > 1404141054508s "

delete from bootstrap where time > 1404141054508s and duration > 300000 "

delete from bootstrap where duration > 30000 and time > 1s"

Documentation reference

https://docs.influxdata.com/influxdb/v2.0/query-data/influxql/

Update

Additional queries

delete from bootstrap where time > 1404141416824 and duration > 3000;
delete sequence_number from bootstrap where time > 1s and duration > 1000;

Maybe this is a bug?

https://github.com/influxdb/influxdb/issues/975
https://github.com/influxdb/influxdb/issues/84

the problem with your queries are only 2. first you cant delete from anything besides time so:
delete from bootstrap where duration > 3000000
dont work, and with this query
delete from bootstrap where time > 1404141416824s
you say that are in seconds but that time is in miliseconds so you have to change to ms :) — Ricardo Origin, Dec 15 '15 at 13:48
Hey @spuder, the accepted answer is wrong. Any chance you can select John Clements' answer as the right one? — Dan Dascalescu, Sep 21 '16 at 00:18

score 57 · Accepted Answer · answered Sep 14 '15 at 05:22

57

It appears that you can do this in influxdb 0.9. For instance, here's a query that just succeeded for me:

DROP SERIES FROM temperature WHERE machine='zagbar'

(Per generous comment by @MuratCorlu, I'm reposting my earlier comment as an answer...)

answered Sep 14 '15 at 05:22

John Clements

16,895
3
37
52

Strangely, this is now *not* working for me (slightly different query), signalling error "ERR: database not open" – John Clements Oct 03 '15 at 22:24
Looks related (identical?) to https://github.com/influxdb/influxdb/issues/3087 , and in similar fashion, I see that while I did get an error, the deletion did actually succeed. – John Clements Oct 03 '15 at 22:25
8

I fail to understand how dropping a series is an answer when the question included a timespan? – Ryan Leach Feb 28 '18 at 05:23
It looks to me like you could specify a timespan using a pair of WHERE clauses, no? (NB: Not trying it, don't have influxDB installed ATM). – John Clements Apr 03 '19 at 19:54
I'm no longer employed at the place where this question has relevance, so can't test. – Ryan Leach Apr 04 '19 at 17:18

spuder · Answer 2 · 2016-09-21T01:05:21.007

30

With influx, you can only delete by time

For example, the following are invalid:

#Wrong
DELETE FROM foo WHERE time < '2014-06-30' and duration > 1000 #Can't delete if where clause has non time entity

This is how I was able to delete the data

DELETE FROM foo WHERE time > '2014-06-30' and time < '2014-06-30 15:16:01'

Update: this worked on influx 8. Supposedly it doesn't work on influx 9

edited Sep 21 '16 at 01:05

answered Oct 09 '14 at 16:08

spuder

17,437
19
87
153

3

I also found out with dismay that Influxdb currently only supports time based query. – dminer Mar 09 '15 at 15:30
What doesn't make sense to me is that this query has time: DELETE from foo where time = '2014-06-30 12:18:00' But that doesn't work. I did try the time > time < and it didn't complain about time but removed ALL data that was greater than time > – VMcPherron May 23 '15 at 15:13
2

Yes, I found that influx is buggy about time. You can't specify a specific time, you need to give it a range that surrounds the date you want. – spuder May 23 '15 at 15:28
Hi there, then I can't delete rows by "container_name", can I? – Undolog May 25 '15 at 10:28
2

@spuder, I think you can add a link to official [documentation](http://influxdb.com/docs/v0.7/api/query_language.html#deleting-data-or-dropping-series). I suppose it may be useful for somebody who are looking for a answer. – Jimilian Jun 03 '15 at 09:25
Looks like influxdb 0.9 may allow a more flexible query language. Hopefully someone who knows more than I do can confirm this.... – John Clements Sep 01 '15 at 23:53
3

It appears that you can do this in influxdb 0.9. For instance, here's a query that just succeeded for me: "DROP SERIES FROM temperature WHERE machine='zagbar';" (sorry about the semicolon, it's a reflex...) – John Clements Sep 02 '15 at 00:02
Hey @JohnClements. It worked for me. Add your comment as a new answer to make it more visible. Thanks – Murat Çorlu Sep 11 '15 at 20:02
+1 to @JohnClements for typing an explanation for the semicolon, instead of taking the easy way out (backspace). Now I don't feel alone. – Jason Jul 07 '16 at 00:26

score 17 · Answer 3 · edited Sep 15 '21 at 06:39

17

I'm surprised that nobody has mentioned InfluxDB retention policies for automatic data removal. You can set a default retention policy and also set them on a per-database level.

From the docs:

CREATE RETENTION POLICY <retention_policy_name> ON <database_name> DURATION <duration> REPLICATION <n> [DEFAULT]

edited Sep 15 '21 at 06:39

chloeupgrow

5
2

answered Dec 30 '15 at 15:08

Dan Esparza

28,047
29
99
127

2

To avoid any potential confusion, these retention policy links are for InfluxDB 0.9, the original poster is using InfluxDB 0.8. That said, in InfluxDB 0.9, retention policies are absolutely the first and best way to remove data, but of course they have to be pre-configured. – beckettsean Jan 05 '16 at 23:46
2

Also, retention policies only remove data once they are set, they do not remove previous data - however, that data is under a different retention policy and can be dropped separately since the old and new RP's data do not overlap (unless you deliberately and manually copy from one to the other) – Julian Knight Apr 29 '16 at 00:14

Jason · Answer 4 · 2016-01-14T18:33:46.983

9

Because InfluxDB is a bit painful about deletes, we use a schema that has a boolean field called "ForUse", which looks like this when posting via the line protocol (v0.9):

your_measurement,your_tag=foo ForUse=TRUE,value=123.5 1262304000000000000

You can overwrite the same measurement, tag key, and time with whatever field keys you send, so we do "deletes" by setting "ForUse" to false, and letting retention policy keep the database size under control.

Since the overwrite happens seamlessly, you can retroactively add the schema too. Noice.

edited Jan 14 '16 at 18:33

answered Jan 14 '16 at 11:27

Jason

2,507
20
25

Where there's a will, there's a way. – user7817808 Jul 06 '17 at 22:06
4

Fields aren't indexed though, so this way there's a per-deleted-entry cost for each query. Which may or may not be problematic. – Tommy Sep 20 '17 at 20:42
Lovely. I never thought that you can "delete" by inserting a "new" point by using a different boolean value! Brilliant! – sivabudh Jul 21 '18 at 16:36
This is great idea, but why do you do it with a field instead of tag? – max pleaner Jul 20 '19 at 06:49
2

@maxpleaner - I used a field instead of a tag because if I used a tag, it would appear as a new entry. e.g. `site=north,cabinet=3,server=2,foruse=True` and `site=north,cabinet=3,server=2,foruse=False` would be two entirely different records. – Jason Jul 20 '19 at 10:09

score 5 · Answer 5 · answered May 26 '21 at 08:42

5

In InfluxDB 2, InfluxQL commands like DROP don't exist (only InfluxQL read-only queries are supported). Instead, you need to use the CLI or REST API.

Example:

influx delete --bucket "MY BUCKET" --predicate '_measurement="MY_MEASUREMENT"' -o "MY ORG" --start '1970-01-01T00:00:00Z' --stop '2025-12-31T23:59:00Z'

answered May 26 '21 at 08:42

jrc

20,354
10
69
64

1

I had to add the token field for it to work. – louis xie Jul 16 '21 at 02:04

Benyamin Jafari · Answer 6 · 2022-08-04T08:25:16.187

4

Run influxdb and select the database:

influx -database '<database-name>'

Then run the query:

DELETE WHERE time < '2021-04-11 7:00:00'

Additionally, if you want to delete data from a specific measurement, do as follows:

DELETE FROM <measurement> WHERE time > '2014-06-30' and time < '2021-04-10 15:16:01'

edited Aug 04 '22 at 08:25

answered Apr 11 '21 at 07:09

Benyamin Jafari

27,880
26
135
150

There's a typo there if you're wondering why you couldn't run it ;) – minhng99 Aug 04 '22 at 03:22
@minhng99 I found the typo and fixed that ;) – Benyamin Jafari Aug 04 '22 at 08:27

Arindam Roychowdhury · Answer 7 · 2020-08-23T14:16:46.990

3

This is for InfluxDB shell version: 1.8.2

Delete works without time field too. As you can see from the series of screen shots:

I create a DB and and add start using it.

Add some rows in it.Verify if they are added.

Delete all with tag 'Dev1' and verify the same.

Note: The tag name has to be in single quotes only. Not double.

edited Aug 23 '20 at 14:16

answered Aug 20 '20 at 08:45

Arindam Roychowdhury

5,927
5
55
63

score 2 · Answer 8 · answered Jul 20 '19 at 18:31

The accepted answer (DROP SERIES) will work for many cases, but will not work if the records you need to delete are distributed among many time ranges and tag sets.

A more general purpose approach (albeit a slower one) is to issue the delete queries one-by-one, with the use of another programming language.

Query for all the records you need to delete (or use some filtering logic in your script)
For each of the records you want to delete:
1. Extract the time and the tag set (ignore the fields)
2. Format this into a query, e.g.
```
DELETE FROM "things" WHERE time=123123123 AND tag1='val' AND tag2='val'
```
  Send each of the queries one at a time

score 1 · Answer 9 · answered Jun 22 '18 at 09:15

1

You can only delete with your time field, which is a number.

Delete from <measurement> where time=123456

will work. Remember not to give single quotes or double quotes. Its a number.

answered Jun 22 '18 at 09:15

Mahaveer Jangir

597
7
15

This is not true, you can specify tags in the delete query as well. – max pleaner Jul 20 '19 at 17:32

score 1 · Answer 10 · answered Jan 29 '22 at 23:11

1

Adding to already given nice answers; If you are using a version >2.* and can use its UI (usually on port 8086):

Go to "Data"
Select "Buckets"
And also select the bucket you want to manage
and give a retention time as described below;

answered Jan 29 '22 at 23:11

myuce

1,321
1
19
29

score -2 · Answer 11 · answered Mar 14 '18 at 09:03

I am adding this commands as reference for altering retention inside of InfluxDB container in kubernetes k8s. wget is used so as container doesn't have curl and influx CLI

wget 'localhost:8086/query?pretty=true' --post-data="db=k8s;q=ALTER RETENTION POLICY \"default\" on \"k8s\" duration 5h shard duration 4h default" -O-

Verification

wget 'localhost:8086/query?pretty=true' --post-data="db=k8s;q=SHOW RETENTION POLICIES" -O-

Can you delete data from influxdb?

I want to delete these 5 entries whos duration > 1000 seconds

11 Answers11

Linked