In this post we recap the week’s most interesting InfluxDB and TICK-stack related issues, workarounds, time range and timestamps how-tos and Q&A from GitHub, IRC and the InfluxDB Google Group that you might have missed.
Query data that occurs in the future
Q: InfluxDB doesn’t return anything when I try to select everything in my measurement:
> INSERT projections happiness=9 1545936300000000000 > INSERT projections happiness=8 1545936360000000000 > INSERT projections happiness=9 1545936420000000000 > SELECT * FROM projections >
A: By default, InfluxQL queries cover the time range between `epoch 0` and `now()`. `now()` is your server’s current timestamp. Your query returns no results because all of your data occur after `now()`.
The following query asks InfluxDB to return all data that occur between `now()` and 156 weeks from `now()`:
> SELECT * FROM projections WHERE time > now() AND time <= now() + 156w name: projections ----------------- time happiness 2018-12-27T18:45:00Z 9 2018-12-27T18:46:00Z 8 2018-12-27T18:47:00Z 9
Find the earliest and latest timestamp in your data
Q: How can I get the first timestamp in my data and last timestamp in my data? The functions `first()` and` last()` return `epoch 0`:
> SELECT first(usage_idle) FROM cpu WHERE cpu='cpu-total' name: cpu --------- time first 1970-01-01T00:00:00Z 97.65 > SELECT last(usage_idle) FROM cpu WHERE cpu='cpu-total' name: cpu --------- time last 1970-01-01T00:00:00Z 96.90077480629843
A: This behavior will be fixed. As a workaround, use `LIMIT` in your query to get the first timestamp and use a combination of `ORDER BY` time `DESC` and `LIMIT` to get the last timestamp:
> SELECT usage_idle FROM cpu WHERE cpu='cpu-total' LIMIT 1 name: cpu --------- time usage_idle 2016-04-11T17:36:30Z 97.65
> SELECT usage_idle FROM cpu WHERE cpu='cpu-total' ORDER BY time DESC LIMIT 1 name: cpu --------- time usage_idle 2016-04-27T19:15:25Z 96.90077480629843
Calculate the duration between timestamps
Q: How can I calculate the difference between the timestamps below?
name: response -------------- time ticket value 2016-04-27T16:45:01Z 1 1 2016-04-27T18:58:05Z 1 2
A: InfluxDB version 0.13 will have the new `elapsed()` function. `elapsed()` returns the difference between subsequent timestamps in a single field.
The following query returns the difference (in seconds) between your two points:
> SELECT elapsed(value,1s) FROM response WHERE ticket='1' name: response -------------- time elapsed 2016-04-27T18:58:05Z 7984
InfluxDB 0.13 is available in the nightly downloads.