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?

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?