TL;DR InfluxDB Tech Tips - InfluxDB Aggregation Function
By
Regan Kuchan /
Developer
Jun 23, 2016
Navigate to:
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?
A: Yes! 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 p
:
> 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.
What's next?
- 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.