TL;DR InfluxDB Tech Tips – July 28, 2016

Regan Kuchan — July 28, 2016

In this post we recap the week’s most interesting InfluxDB and TICK-stack related issues, workarounds, how-tos and Q&A from GitHub, IRC and the InfluxDB Google Group that you might have missed.

Continuous Queries vs. Raw INTO Queries

Q: I want to automatically downsample data from one measurement and save those downsampled data in another measurement. I’ve written a Continuous Query (CQ) but it doesn’t seem to be processing my data. Is my syntax correct and is my understanding of CQs accurate?

My CQ:

> CREATE CONTINUOUS QUERY "downsample" ON "water_study" BEGIN SELECT max("sediment") INTO "downsampled_data" FROM "study1" WHERE time > now() - 1d GROUP BY time(30m),"quad" END

After I let it run for a while, I notice that InfluxDB never creates the measurement `downsampled_data`:
name: measurements

Note that there are data in the original measurement `study1`:
> SELECT * FROM study1 LIMIT 4
name: study1
time                  quad  sediment
2016-07-27T15:00:00Z  1     5
2016-07-27T15:00:00Z  4     7
2016-07-27T15:00:00Z  3     4
2016-07-27T15:00:00Z  2     3

A: Focusing on the `WHERE` clause in your CQ, it looks like you’re trying to execute a CQ on older data, that is, data with timestamps that occur before the time at which you created the CQ. Including `WHERE time > now() – 1d` in your CQ won’t work for a couple reasons:

  • CQs ignore time ranges in the `WHERE` clause
  • CQs only run on new data, that is, data with timestamps that occur after the time at which you create the CQ

To downsample older data use a basic `INTO` query. Unlike CQs, raw `INTO` queries accept a time range in the `WHERE` clause and will work on older data:

> SELECT max("sediment") INTO "downsampled_data" FROM "study1" WHERE time > now() - 1d GROUP BY time(30m),"quad"
name: result
time                  written
1970-01-01T00:00:00Z  8

> SELECT * FROM "downsampled_data" LIMIT 4
name: downsampled_data
time                  max  quad
2016-07-27T15:00:00Z  6    1
2016-07-27T15:00:00Z  8    4
2016-07-27T15:00:00Z  4    3
2016-07-27T15:00:00Z  3    2

Timestamp Precision when Writing Data

Q: I’m able to successfully write data to InfluxDB using the HTTP API but my timestamps are never what I expect them to be. I’m using epoch time – does Line Protocol accept different timestamp formats?

Write data with a timestamp for July 7, 2016, 14:00:00:

$ curl -POST "http://localhost:8086/write?db=test" --data-binary 'furniture futon=2 1469628000'

Query the data and see a timestamp in the 1970s:
> SELECT * FROM "furniture"
name: furniture
time                         futon
1970-01-01T00:00:01.469628Z  2

A: Line protocol only accepts epoch timestamps, but, by default, it assumes that the timestamp is in nanoseconds. Your timestamp, however, is in seconds. Use the query string parameter `precision` to specify an alternative timestamp precision:

$ curl -POST "http://localhost:8086/write?db=test&precision=s" --data-binary 'furniture futon=2 1469628000'

> SELECT * FROM "furniture"
name: furniture
time                   futon
2016-07-27T14:00:00Z   2

Check out the API Reference for more information on query string parameters.

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?