TL;DR InfluxDB Tech Tips - InfluxDB Aggregation Function
By Regan Kuchan / Jun 23, 2016 / Tech Tips
In this post we recap the week’s most interesting InfluxDB aggregation function and TICK-stack related issues, workarounds, how-tos and Q&A from GitHub, IRC and the InfluxDB Google Group that you might have missed.
Understanding the timestamps returned by aggregation functions
Q: Why does the following query return a
1970-01-01T00:00:00Z timestamp? None of my data have that timestamp.
> SELECT mean(lions) FROM animals name: animals ------------- time mean 1970-01-01T00:00:00Z 9.333333333333334
A: InfluxDB uses epoch 0 (
1970-01-01T00:00:00Z) as a null-timestamp. A query will return epoch 0 if it doesn’t have a specific timestamp to return.
Aggregation functions, like
mean(), don’t have a specific timestamp to return. Aggregation functions return epoch 0 if you don’t include a time range in the
WHERE clause. If you include a time range in the
WHERE clause, aggregation functions return the first timestamp in the time range. For example:
> SELECT mean(lions) FROM animals WHERE time >= '2016-06-22T21:57:23Z' AND time <= '2016-06-22T21:57:32Z' name: animals ------------- time mean 2016-06-22T21:57:23Z 9.333333333333334
Improving query performance
Q: The following query takes a while (about five seconds) to run. I expected it to run in milliseconds. Do you have any advice?
SELECT mean("temp") FROM "coffee" WHERE time = '2016-06-22T00:45:15Z' AND office='a' GROUP BY time(1s),office ORDER BY time DESC
A: You can simplify your query and shorten how long it takes to run by removing the
GROUP BY clause and
ORDER BY time DESC.
You don’t need to
GROUP BY time(1s) because you query a specific timestamp in the
WHERE clause. Similarly, you don’t need to
GROUP BY office because you query a specific
office in the
WHERE clause. Lastly, you don’t need
ORDER BY time DESC because your query returns a single point.
SELECT mean("temp") FROM "coffee" WHERE time = '2016-06-22T00:45:15Z' AND office='a'
Using regular expressions in SHOW TAG VALUES
Q: Is there a way to use regular expressions in the
SHOW TAG VALUES query?
SHOW TAG VALUES supports regular expressions in the
WITH KEY clause. For example, return the tag values for all tag keys that do not include the letter
> SHOW TAG VALUES WITH KEY !~ /.*p.*/ name: mymeas ------------ key value forte 4 salt 1 salt 2
The following query should be faster:For more InfluxDB tips, check out our Frequently Asked Questions page and feel free to post your questions in the InfluxDB users group.
- Looking for InfluxDB clustering on your infrastructure? Get started with InfluxDB Enterprise Beta, now available for evaluation.
- Download and get started with InfluxDB 1.0 Beta 2
- Schedule a FREE 20 minute consultation with a Solutions Architect to review your InfluxDB project
- Attend one of our FREE virtual training seminars.