TL;DR InfluxDB Tech Tips - Eliminate Blank Field Values in Query Results & More!
By
Regan Kuchan /
Product, Developer
Feb 16, 2017
Navigate to:
In this weekly post we recap the most interesting InfluxDB blank values and TICK-stack related issues, workarounds, how-tos and Q&A from GitHub, IRC and the InfluxDB Google Group that you might have missed in the last week or so.
Eliminate blank field values in query results
Q: I’m excited to work with the GROUP BY time() clause but I’ve noticed that my queries return a lot of results with blank field values. It looks like the system returns a timestamp for every GROUP BY time() interval in the query’s time range regardless of if there’s a value for that interval. I’m working with irregular time series data - is there a way to get around that GROUP BY time() behavior and only get results with actual field values?
My query:
> SELECT SUM("candied") FROM "hearts" \ WHERE time >= '2017-02-14T16:00:00Z' AND time <= '2017-02-14T22:00:00Z' \ GROUP BY time(1h) name: hearts time sum ---- --- 2017-02-14T16:00:00Z 4 2017-02-14T17:00:00Z 12 2017-02-14T18:00:00Z 2017-02-14T19:00:00Z 2017-02-14T20:00:00Z 2017-02-14T21:00:00Z 2017-02-14T22:00:00Z 23
A: Yes! InfluxQL supports fill(<fill_options>) which replaces those blank values with the relevant fill_option. One of those options is none; a query with fill(none) reports no timestamp and no value for time intervals with no data.
> SELECT SUM("candied") FROM "hearts" \ WHERE time >= '2017-02-14T16:00:00Z' AND time <= '2017-02-14T22:00:00Z' \ GROUP BY time(1h) fill(none) name: hearts time sum ---- --- 2017-02-14T16:00:00Z 4 2017-02-14T17:00:00Z 12 2017-02-14T22:00:00Z 23
Other fill_options include replacing blank values with:
- the numerical value of your choice
- the results of linear interpolation
- the results from the previous interval
Check out the Data Exploration page in the documentation for more information.
Calculate the time elapsed between FIRST() and LAST() values
Q: I want to know the elapsed time between the first and last values in a measurement, grouped by a single tag key. When I run the query below, the system overwrites the timestamps associated with the first and last values and I can’t even use the ELAPSED() function :(. Any ideas on how I can get the information I want?
> SELECT FIRST("bouquets"),LAST("bouquets") FROM "flower_purchases" \ GROUP BY "store_id" name: flower_purchases tags: store_id=1 time first last ---- ----- ---- 1970-01-01T00:00:00Z 3 5 name: flower_purchases tags: store_id=2 time first last ---- ----- ---- 1970-01-01T00:00:00Z 10 12
A: The workaround takes a couple steps but there is a way to get the information that you’re after with InfluxQL.
In the codeblock below, we get the results in three main steps. The first query writes the results of the FIRST() function to a new measurement and field key. The second query writes the results of the LAST() function to that same measurement and field key. The final query calculates the elapsed time using the results of the first and second queries.
# Step 1: Write the FIRST() values to the
# for_elapsed measurement and first_lasts field key
> SELECT FIRST("bouquets") AS "first_lasts" INTO "for_elapsed" \
FROM "flower_purchases" GROUP BY "store_id"
name: result
time written
---- -------
1970-01-01T00:00:00Z 2
> SELECT "first_lasts","store_id" FROM "for_elapsed"
name: for_elapsed
time first_lasts store_id
---- ----------- --------
2017-02-14T22:00:00Z 3 1
2017-02-14T22:15:00Z 10 2
# Step 2: Write the LAST() values to the
# for_elapsed measurement and first_lasts field key
> SELECT LAST("bouquets") AS "first_lasts" INTO "for_elapsed" \
FROM "flower_purchases" GROUP BY "store_id"
name: result
time written
---- -------
1970-01-01T00:00:00Z 2
> SELECT "first_lasts","store_id" FROM "for_elapsed"
name: for_elapsed
time first_lasts store_id
---- ----------- --------
2017-02-14T22:00:00Z 3 1
2017-02-14T22:15:00Z 10 2
2017-02-14T22:30:00Z 5 1
2017-02-14T22:35:00Z 12 2
# Step 3: Calculate the ELAPSED() time on the data in the
# for_elapsed measurement and first_lasts field key
> SELECT ELAPSED("first_lasts",1m) FROM "for_elapsed" GROUP BY "store_id"
name: for_elapsed
tags: store_id=1
time elapsed
---- -------
2017-02-14T22:30:00Z 30
name: for_elapsed
tags: store_id=2
time elapsed
---- -------
2017-02-14T22:35:00Z 20See the Data Exploration page in the documentation for more on the INTO clause.
Calculate the SUM() of the difference between MIN() and MAX() values
Q: I want to calculate the SUM() of the differences between MIN() and MAX() values in a field. I get a parsing error when I run the query below and, upon further investigation, it seems like InfluxQL doesn’t support nesting with SUM(). Do you know of a way to work around this?
> SELECT SUM(MAX("heart_rate")-MIN("heart_rate")) FROM "health" GROUP BY "id" ERR: error parsing query: expected field argument in sum()
A: Starting with InfluxDB version 1.2, InfluxQL supports subqueries; subqueries will give you the results you’re looking for!
> SELECT SUM("difference") FROM \ (SELECT MAX("heart_rate") - MIN("heart_rate") \ AS "difference" FROM "health" GROUP BY "id") name: health time sum ---- --- 1970-01-01T00:00:00Z 20
Check out the Data Exploration page in the documentation for more information.
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.
