Query returning wrong data

influxql
#1

Hi all, Influxdb is not behaving properly.
I query for the data in the previous 5 minutes, but it is returning me too much data and that too from all the time today.

See in the picture, I have queried the data only of previous 5 minutes. The time of query was 18:02, and it has returned me data from 14:41 , which is nowhere near the previous 5 minutes.

I have to see this data in the map and this data is very crucial for me. Can you tell me what is wrong here, me or influxdb ?

#2

@Luv This looks like a possible bug with ORDER BY. Can you open an issue on InfluxDB?

#3

Can tags be stored as integer ?

#4

@Luv Tags are only stored as strings.

#5

I have stored the speed of the vehicles as a tag, so it means I cannot perform less than OR greater than query on them ? So, it is not possible ?

#6

@Luv That is definitely a non recommended schema choice. Tags are only stored as strings. Storing speed as a tag will also lead to an explosion of series cardinality. If you need to filter results based on speed I would suggest using a sub-query.

#7

@jackzampolin

Suppose if I store speed as field, and perform ( greater than and less than ) query, how will the performance of the query be affected ?

As tags are indexed but fields are not, meaning that field query will take more time.

I am talking about the data of 300 vehicles recorded every minute. So, the query performance will matter as data amount is very high.

#8

@Luv The fastest way to do that would be to use a subquery to filter the results of a more general query. I would encourage you to test it and see how performance is affected.

I will reiterate that writing numeric data as a tag is an actively discouraged schema. That is not the way the database is designed to used.

#9

How much data does each vehicle report each minute?

#10

There are 250 vehicles for now. The IoT sensor sends data every minute or 2 about its location, time, speed and its number. I calculate geohash by python library, and that is also stored.

The vehicles will probably reach 400 count in next year.

#11

For that resolution of data, having the speed stored as a field shouldn’t impact performance. InfluxDB can scan through 1-3 million field values per second.

Additionally, as @jackzampolin noted, storing them as fields will increase the series cardinality drastically and you care about the numeric property of the value which you lose if you store it as a tag.