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.
- Looking for InfluxDB clustering on your infrastructure? Get started with InfluxEnterprise 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.