TL;DR InfluxDB Tech Tips – InfluxDB Aggregation Function

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.