TL;DR InfluxDB Tech Tips - Understanding Syntax, Continuous Queries and Timestamp error

By Regan Kuchan / Developer
Oct 13, 2016

Navigate to:

In this weekly post we recap the most interesting InfluxDB and syntax, continuous queries and bad timestamp 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.

Mixing aggregates with non-aggregates in InfluxQL

Q: I’m running queries with a function and a separate tag value. This works fine when I use the max() function, but when I use the sum() function I get the error: mixing aggregate and non-aggregate queries is not supported. I don’t think my syntax is incorrect; what am I missing?

Raw data:

> SELECT * FROM "halloween_c?ndy"
name: halloween_c?ndy
------------------------
time                   candied_?pples   headache_index
2016-10-12T17:02:00Z   2                 0.5
2016-10-12T17:03:00Z   3                 1.2
2016-10-12T17:04:00Z   0.5               1.0

Queries:

> SELECT max("candied_?pples"),"headache_index" FROM "halloween_c?ndy"
name: halloween_c?ndy
------------------------
time                   max   headache_index
2016-10-12T17:03:00Z   3     1.2

> SELECT sum("candied_?pples"),"headache_index" FROM "halloween_c?ndy"
ERR: error parsing query: mixing aggregate and non-aggregate queries is not supported

A: The function in your first query (max()) is a selector function. Selector functions return a single raw value. The function in your second query (sum()) is an aggregator function. Aggregator functions return a single calculated value.

InfluxDB doesn’t support using an aggregator function and a standalone tag key or field key in the same SELECT statement. Because aggregator functions return a calculated value there’s no obvious single value to return for the unaggregated tag or field.

In your example, the first query returns the maximum value of candied_?pples (3) and the value of headache_index when candied_?pples equals 3 (1.2). The second query would return the sum of the three values of candied_?pples (5.5), but there’s no obvious single value to return for the raw headache_index tag.

Altering a Continuous Query

Q: Can I  update a Continuous Query after I’ve created it?

A: Currently, InfluxDB does not have an ALTER CONTINUOUS QUERY statement. To change an existing Continuous Query, you’ll need to DROP and then reCREATE the Continuous Query.

Understanding when bad timestamp means bad syntax

Q: I’m trying to write data to InfluxDB and I keep getting a bad timestamp error. The only thing is, my line protocol doesn’t have any timestamps. I thought InfluxDB would automatically assign the server’s local timestamp to the point if I didn’t provide a timestamp; is this wrong?

Example:

> INSERT ghouls likes=12 overall_popularity_index=3
ERR: {"error":"unable to parse 'ghouls likes=12 overall_popularity_index=3': bad timestamp"}

A: You’re correct that InfluxDB automatically assigns the server’s local nanosecond timestamp in UTC to a point if the line protocol doesn’t include a timestamp. The bad timestamp error that you’re experiencing is actually the result of a syntax error:

Line protocol is whitespace sensitive. Because of the space between likes=12 and overall_popularity_index=3, InfluxDB assumes that overall_popularity_index=3 is the timestamp, and overall_popularity_index=3 is, in fact, an invalid timestamp.

Separating likes=12 and overall_popularity_index=3 with a comma instead of a white space would fix your issue (this assumes that those two elements are fields):

> INSERT ghouls likes=12,overall_popularity_index=3

Check out the line protocol tutorial doc for more on line protocol syntax.

For more InfluxDB tips, see our Frequently Asked Questions page and feel free to post your questions in the InfluxDB users group!

What's next?

  • Download and get started with InfluxDB!
  • Schedule a FREE 20 minute consultation with a Solutions Architect to review your InfluxDB project.
  • Attend one of our FREE virtual training seminars.
  • Got a question and need an immediate answer from the InfluxData Support team? Support subscriptions with unlimited incidents start at just $399 a month. Check out all the support options here.