TL;DR InfluxDB Tech Tips - Customizing GROUP by Time() Windows & Series Cardinality

By Regan Kuchan / Developer
May 13, 2016

Navigate to:

In this post we recap the week’s most interesting InfluxDB and TICK-stack related issues, workarounds with customizing GROUP by time() windows & series cardinality, how-tos and Q&A from GitHub, IRC and the InfluxDB Google Group that you might have missed.

Regular expressions in the WHERE clause

Q: I’m trying to write a query with a wildcard in the WHERE clause and I can’t seem to get it to work.

I’m able to use a regex in the FROM clause:

> SELECT mean("species") FROM /rainforest*/ WHERE "location" = 'q01'

But this query returns an error:

> SELECT mean("species") FROM /rainforest*/ WHERE "location" = /q*/
ERR: error parsing query: found /, expected identifier, string, number, bool at line 1, char 62

A: InfluxQL requires regular expressions in the WHERE clause. You need to use the proper operator for regex, which is =~ for positive matches, and !~ for negative matches.

For example:

> SELECT mean("species") FROM /rainforest*/ WHERE "location" =~ /q*/

Series cardinality and retention policies

Q: Do retention policies affect series cardinality?

I’m trying to limit my series cardinality for performance purposes. I’m planning on having several retention policies and I’d like to know if that has an effect on series cardinality.

A: Retention policies do not multiply series cardinality as the series index only applies to unique measurement and tagset combinations. Whether a measurement-tagset combination is in every retention policy or just one, it’s still only a single series for the index, and thus for the cardinality concerns.

Customizing GROUP BY time() windows

Q: Is there any way to change the default time windows returned by a GROUP BY time() query?

A: Starting with InfluxDB version 0.13, you can alter GROUP BY time()’s default rounded calendar time boundaries by including an offset interval.

The syntax is:

SELECT [...] GROUP BY time(time_interval,offset_interval)

See Data Exploration for a more detailed description and examples.

For more InfluxDB tips, check out our Frequently Encountered Issues page and feel free to post your questions in the InfluxDB users group.

What's next?