TL;DR InfluxDB Tech Tips - Time Range and Timestamps

By Regan Kuchan / Developer
Apr 28, 2016

Navigate to:

To learn about Flux Time Ranges and Flux Time Stamps, check out these blogs.

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:

First timestamp:

> SELECT usage_idle FROM cpu WHERE cpu='cpu-total' LIMIT 1
name: cpu
---------
time                  usage_idle
2016-04-11T17:36:30Z  97.65

Last timestamp:

> 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.

For more InfluxDB tips, check out our Frequently Encountered Issues page and feel free to post your questions in the InfluxDB users group.

What's next?

  • Download and get started with InfluxDB!
  • Attend one of our FREE virtual training seminars.
  • Got a question and need an immediate answer from the InfluxData Support team? Support subscriptions with unlimited incidents start at just $399 a month. Check out all the support options here.