TL;DR InfluxDB Tech Tips - Downsample Data Within Continuous Query & Raw INTO Query

By Regan Kuchan / Developer
Jul 28, 2016

Navigate to:

In this post we recap the week’s most interesting InfluxDB and TICK-stack related issues, workarounds, how-to downsample data within continuous query & raw INTO query 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`:
> SHOW MEASUREMENTS
name: measurements
------------------
name
study1 

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?