TL;DR InfluxDB Tech Tips - How To Return Readable Timestamps in the CLI

By Regan Kuchan / Developer
Apr 13, 2016

Navigate to:

In this post we recap the week’s most interesting InfluxDB and TICK-stack related issues, workarounds, how-to return readable Timestamps in the CLI and Q&A from GitHub, IRC and the InfluxDB Google Group that you might have missed.

Human-readable timestamps in the CLI

Question: How do you make InfluxDB’s CLI return human readable timestamps?

Instead of this:

name: clothes
---------
time                 percent_cool
892482496000000000   0.5

Return this:

name: clothes
---------
time                   percent_cool
1998-04-13T15:48:16Z   0.5

Answer: When you first connect to the CLI, specify the rfc3339 precision:

$ influx -precision rfc3339

Alternatively, specify the precision once you’ve already connected to the CLI:

$ influx
Connected to http://localhost:8086 version 0.xx.x
InfluxDB shell 0.xx.x
> precision rfc3339
>

Check out our docs for more useful CLI options.

Calculate the average of the difference between two fields

Question: How do you calculate the average of the difference between two fields? This query returns an error:

> SELECT mean(field1 - field2) from my_measurement

Answer: Currently, InfluxQL can’t do this in one query. We recommend first, calculating the difference and writing the results to a new field and second, calculating the average of that new field.

Example:

# Write some data
> INSERT pet_daycare cats=5,dogs=7
> INSERT pet_daycare cats=6,dogs=9
> SELECT * FROM pet_daycare
name: pet_daycare
-----------------
time                             cats   dogs
2016-04-13T16:10:19.237337894Z   5      7
2016-04-13T16:10:25.844917838Z   6      9

# Calculate the difference and write the results to the same measurement using INTO
> SELECT dogs - cats INTO pet_daycare FROM pet_daycare
name: result
------------
time                   written
1970-01-01T00:00:00Z   2

> SELECT * FROM pet_daycare
name: pet_daycare
-----------------
time                             cats   dogs   dogs_cats
2016-04-13T16:10:19.237337894Z   5      7      2
2016-04-13T16:10:25.844917838Z   6      9      3

# Calculate the average of the new field
> SELECT mean(dogs_cats) FROM pet_daycare
name: pet_daycare
-----------------
time                   mean
1970-01-01T00:00:00Z   2.5

For more, check out our docs on InfluxQL functions and INTO queries.

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.