TL;DR InfluxDB Tech Tips - InfluxQL Support For Linear Interpolation in GROUP BY Time() Queries

By Regan Kuchan / Developer
Nov 17, 2016

Navigate to:

In this post we recap some of the new features that you might have missed in the most recent InfluxDB release, including support for linear interpolation in GROUP BY Time() queries. Check in next week for more on the most interesting InfluxDB and TICK-stack related issues, workarounds, how-tos and Q&A from GitHub, IRC and the InfluxDB Google Group.

You may have heard that we recently released InfluxDB v1.1. In addition to various performance and stability improvements, version 1.1 offers several new features and syntax developments. Here are a couple updates that you might have missed:

InfluxQL support for mixed duration units

Gone are the days of having to specify durations in retention policies, continuous queries, and time conditions with single units only. In version 1.1, InfluxDB supports using mixed duration units. The example below shows off this seemly small, but useful improvement to the query language.

In versions prior to 1.1, a retention policy duration of one week and four days had to be translated to 11 days or 264 hours. In versions 1.1+, you can specify the duration with mixed units.

Old duration syntax:

> CREATE RETENTION POLICY "a_week_and_four_days" ON "duration_nation" DURATION 264h REPLICATION 1
                                                                               ----

New duration syntax:

> CREATE RETENTION POLICY "a_week_and_four_days" ON "duration_nation" DURATION 1w4d REPLICATION 1
                                                                               ----

Configuration setting for managing series cardinality

Limiting your series cardinality is an essential part of working with InfluxDB. The new max-values-per-tag configuration setting can help you do just that.

The setting limits the number of tag values allowed per tag key. The default setting is 10,000 (so 10,000 tag values allowed per tag key). If a write causes the number of tag values for a tag key to exceed 10,000, InfluxDB will not write the point and it returns a partial write error.

InfluxQL support for linear interpolation in GROUP BY time() queries

Anyone who has worked with GROUP BY time() queries will know that fill() changes the value reported for time intervals without any data. Starting with version 1.1, fill() supports linear interpolation; just include fill(linear) in your query.

A GROUP BY time() query without fill(linear):

> SELECT MEAN("line_dances") FROM "metrics" WHERE time >= '2016-11-11T21:00:00Z' AND time <= '2016-11-11T22:06:00Z' GROUP BY time(12m)

name: metrics time mean —- —- 2016-11-11T21:00:00Z 1 2016-11-11T21:12:00Z 2016-11-11T21:24:00Z 3 2016-11-11T21:36:00Z 2016-11-11T21:48:00Z 2016-11-11T22:00:00Z 6 A GROUP BY time() query with fill(linear):

> SELECT MEAN("line_dances") FROM "metrics" WHERE time >= '2016-11-11T21:00:00Z' AND time <= '2016-11-11T22:06:00Z' GROUP BY time(12m) fill(linear)

name: metrics time mean —- —- 2016-11-11T21:00:00Z 1 2016-11-11T21:12:00Z 2 2016-11-11T21:24:00Z 3 2016-11-11T21:36:00Z 4 2016-11-11T21:48:00Z 5 2016-11-11T22:00:00Z 6

What's next

  • Downloads for the TICK-stack are live on our "downloads" page
  • Deploy on the Cloud: Get started with a FREE trial of InfluxDB Cloud featuring fully-managed clusters, Kapacitor and Grafana.
  • Deploy on Your Servers: Want to run InfluxDB clusters on your servers? Try a FREE 14-day trial of InfluxDB Enterprise featuring an intuitive UI for deploying, monitoring and rebalancing clusters, plus managing backups and restores. 
  • Tell Your Story: Over 100 companies have shared their story on how InfluxDB is helping them succeed. Submit your testimonial and get a limited edition hoodie as a thank you.