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 InfluxEnterprise 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.