TL;DR InfluxDB Tech Tips - Interesting InfluxDB Timestamps Issues and the INTO Clause
By Regan Kuchan / Dec 08, 2016 / Tech Tips
In this weekly post we recap the most interesting InfluxDB timestamps and TICK-stack related issues, functions with an INTO Clause, 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.
Q: I’m entering historical data into InfluxDB. Is it possible to write data with timestamps that occur before epoch 0?
A: Yes! InfluxDB supports negative timestamps. The minimum acceptable timestamp is
1677-09-21T00:12:43.145224194Z. Here’s an example of a negative timestamp in line protocol - the timestamp in human-readable format is
> INSERT holidata snow=3 -2092368000000000
Functions with an INTO clause
Q: When I use
DISTINCT() without an
INTO clause everything works as expected. When I use
DISTINCT() with an
INTO clause I end up missing data points in the destination measurement. Is this the expected behavior? What am I missing? My data:
> SELECT * FROM "influxdays" name: influxdays time value ---- ----- 2016-12-01T19:06:50Z fa 2016-12-04T19:12:19Z fa 2016-12-14T19:06:50Z fa la 2016-12-17T19:06:50Z fa la la
> SELECT DISTINCT("value") FROM "influxdays" name: influxdays time distinct ---- -------- 1970-01-01T00:00:00Z fa 1970-01-01T00:00:00Z fa la <---- There are three distinct values! 1970-01-01T00:00:00Z fa la la
> SELECT DISTINCT("value") INTO "influxdays_copy" FROM "influxdays" name: result time written ---- ------- 1970-01-01T00:00:00Z 3 <---- It says it wrote three points!
The results in the destination measurement:
> SELECT * FROM "influxdays_copy" name: influxdays_copy time distinct ---- -------- 1970-01-01T00:00:00Z fa la la <---- I only see one point!
A: What you’re seeing is the intended behavior, but I can see that it’s not necessarily what you’d expect.
DISTINCT() (also see
BOTTOM()) often return several points with the same timestamp. InfluxDB assumes points with the same series and timestamp are duplicate points so, when you include an
INTO clause in the query, InfluxDB simply overwrites what it thinks are duplicate points in the destination measurement.
COUNT() with fill()
COUNT() replaces any
0 values with the given
> SELECT COUNT("partridge") FROM "gifts" WHERE time >= '2016-12-07T00:00:00Z' AND time <= '2016-12-07T02:00:00Z' GROUP BY time(1h) name: gifts time count ---- ----- 2016-12-07T00:00:00Z 1 2016-12-07T01:00:00Z 2 2016-12-07T02:00:00Z 0
> SELECT COUNT("partridge") FROM "gifts" WHERE time >= '2016-12-07T00:00:00Z' AND time <= '2016-12-07T02:00:00Z' GROUP BY time(1h) fill(30) name: gifts time count ---- ----- 2016-12-07T00:00:00Z 1 2016-12-07T01:00:00Z 2 2016-12-07T02:00:00Z 30 <--- now the zero is 30
- Downloads for the TICK-stack are live on our "downloads" page
- Deploy on the Cloud: Get started with a FREE trial of InfluxDB Cloud featuring fully-managed clusters, Kapacitor and Grafana.
- Deploy on Your Servers: Want to run InfluxDB clusters on your servers? Try a FREE 14-day trial of InfluxDB Enterprise featuring an intuitive UI for deploying, monitoring and rebalancing clusters, plus managing backups and restores.
- Tell Your Story: Over 100 companies have shared their story on how InfluxDB is helping them succeed. Submit your testimonial and get a limited edition hoodie as a thank you.