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?

Pin It on Pinterest

Contact Sales