TL;DR InfluxDB Tech Tips - Time Range and Timestamps
By Regan Kuchan / Apr 28, 2016 / Tech Tips
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() is your server’s current timestamp. Your query returns no results because all of your data occur after
The following query asks InfluxDB to return all data that occur between
now() and 156 weeks from
> 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
> 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
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() 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.