TL;DR InfluxDB Tech Tips - Querying Data in a Non-DEFAULT Retention Policy, Checking Field Types, and Querying vs. Writing Booleans

By Regan Kuchan / Product, Developer
Apr 27, 2017

Navigate to:

In this post, we recap some new material that you might have missed in the past week or so. Check in next week for the most interesting TICK stack related issues, workarounds, how-tos, and Q&A from GitHub, IRC, and the InfluxData Community.

Querying data in a non-DEFAULT retention policy

Q: I created a new retention policy called secret_garden. I started writing data to that retention policy and the logs show that the writes are successful, but when I query the data I get an empty response. What am I doing wrong?

Results of the SHOW RETENTION POLICIES query:

> SHOW RETENTION POLICIES ON "lands"
name	       duration	  shardGroupDuration   replicaN   DEFAULT
autogen	       0          168h0m0s             1          true
secret_garden  24h0m0s	  1h0m0s               1          false

Results of a SELECT query:

> SELECT * FROM "foliage"
>

A: The retention policy secret_garden is not the DEFAULT retention policy for your database (see the fifth column in the SHOW RETENTION POLICIES output).

You’ll need to fully qualify the foliage measurement if you’re querying data in a retention policy that is not the DEFAULT retention policy. Fully qualify a measurement by specifying the database and retention policy in the FROM clause: <"database">.<"retention_policy">.<"measurement">.

Example:

> SELECT * FROM "lands"."secret_garden"."foliage"
name: foliage
-------------
time                             roses
2016-05-31T17:09:04.697144667Z   3
2016-05-31T17:09:07.096708707Z   5

Checking field types

Q: Is there a way to see the data type of a field?

A: Starting with InfluxDB version 1.0, the SHOW FIELD KEYS query also returns the field’s data type.

Example:

> SHOW FIELD KEYS FROM all_the_types
name: all_the_types
-------------------
fieldKey  fieldType
blue      string
green     boolean
orange    integer
yellow    float

Querying vs. writing booleans

Q: I’m able to successfully write booleans to my database, but I can’t seem to use them in my query’s WHERE clause.

> INSERT hamlet tobe=t
> INSERT hamlet tobe=f
> SELECT * FROM "hamlet" WHERE "tobe"=t
>

A: Acceptable boolean syntax differs for writes and for queries.

In your case, InfluxDB understands that t means true when you write your data but it doesn’t understand that t means true when you query your data. You’ll need to use true, True, or TRUE to reference that boolean value in a query.

Example:

> SELECT * FROM "hamlet" WHERE "tobe"=true
name: hamlet
------------
time                             tobe
2016-05-31T18:07:32.93151244Z    true

What's next:

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