TL;DR InfluxDB Tech Tips – InfluxDB Aggregation Function

3 minutes

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`:

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?

Related Blog Posts

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top