TL;DR Tech Tips: New InfluxQL Functions in Version 1.3

By Regan Kuchan / Developer
Jun 15, 2017

Navigate to:

In this post, we give a preview of the new InfluxQL functions in version 1.3. Check in next week for the most interesting TICK Stack related issues, workarounds, how-tos, and Q&A from GitHub, IRC, and the InfluxData Community.

New Function: INTEGRAL()

The INTEGRAL() function returns the area under the curve for subsequent field values. The query below returns the area under the curve (in seconds) for the field values associated with the water_level field key and in the h2o_feet measurement:

> SELECT INTEGRAL("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'

name: h2o_feet
time                  integral
----                  --------
1970-01-01T00:00:00Z  3732.66

New Function: NON_NEGATIVE_DIFFERENCE()

The NON_NEGATIVE_DIFFERENCE() function returns the non-negative result of subtraction between subsequent field values. Non-negative results of subtraction include positive differences and differences that equal zero. The query below returns the non-negative difference between subsequent field values in the water_level field key and in the h2o_feet measurement:

> SELECT NON_NEGATIVE_DIFFERENCE("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet
time                  non_negative_difference
----                  -----------------------
2015-08-18T00:06:00Z  0.052000000000000046
2015-08-18T00:18:00Z  0.09799999999999986
2015-08-18T00:30:00Z  0.010000000000000231

Updated Functions: TOP() and BOTTOM()

Version 1.3 introduces three major changes to the TOP() and BOTTOM functions:

  • TOP() and BOTTOM() no longer support other functions in the SELECT clause. The following query returns an error:
> SELECT TOP(value,1),MEAN(value) FROM "gopher"
  
ERR: error parsing query: selector function top() cannot be combined with other functions
  • TOP() and BOTTOM() now maintain tags as tags if the query includes a tag key as an argument. The query below preserves location as a tag in the newly-written data:
> SELECT BOTTOM("water_level","location",2) INTO "bottom_water_levels" FROM "h2o_feet"

name: result
time                 written
----                 -------
1970-01-01T00:00:00Z 2

> SHOW TAG KEYS FROM "bottom_water_levels"

name: bottom_water_levels
tagKey
------
location
  • TOP() and BOTTOM() now preserve the timestamps in the original data when they're used with the GROUP BY time() clause. The following query returns the points' original timestamps; the timestamps are not forced to match the start of the GROUP BY time() intervals:
> SELECT TOP("water_level",2) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(18m)

name: h2o_feet
time                   top
----                   ------
                           __
2015-08-18T00:00:00Z  2.064 |
2015-08-18T00:06:00Z  2.116 | <------- Greatest points for the first time interval
                           --
                           __
2015-08-18T00:18:00Z  2.126 |
2015-08-18T00:30:00Z  2.051 | <------- Greatest points for the second time interval
                           --

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 300 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.