TL;DR InfluxDB Tech Tips - Downsample Data Within Continuous Query & Raw INTO Query
By Regan Kuchan / Jul 28, 2016 / Tech Tips
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?
> 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.
- Looking for InfluxDB clustering on your infrastructure? Get started with InfluxDB Enterprise Beta, now available for evaluation.
- Download and get started with InfluxDB 1.0 Beta 3
- Schedule a FREE 20 minute consultation with a Solutions Architect to review your InfluxDB project
- Attend one of our FREE virtual training seminars.