Documentation

WHERE clause

Limited availability

InfluxDB Clustered is currently only available to a limited group of InfluxData customers. If interested in being part of the limited access group, please contact the InfluxData Sales team.

Use the WHERE clause to filter data based on field values, tag values, and timestamps.

Syntax

SELECT_clause FROM_clause WHERE <conditional_expression> [(AND|OR) <conditional_expression> [...]]
  • conditional_expression: Comparison between two operands that evaluates to true or false. Comparison logic is determined by operators used in the expression. These expressions can operate on InfluxDB fields, tags, and timestamps. Use logical operators (AND, OR) to chain multiple conditional expressions together.

Operators

Operators evaluate the relationship between two operands and return true or false.

Comparison operators

Operator Meaning Supported data types
= Equal to all
<> Not equal to all
!= Not equal to all
> Greater than numeric, timestamp
>= Greater than or equal to numeric, timestamp
< Less than numeric, timestamp
<= Less than or equal to numeric, timestamp
=~ Matches a regular expression strings
!~ Doesn’t match a regular expression strings

Logical operators

Operator Meaning
AND Returns true if both operands are true. Otherwise, returns false.
OR Returns true if any operand is true. Otherwise, returns false.

Time ranges

Use the WHERE clause to specify a time range to query. If a time range isn’t specified in the WHERE clause, the default time range is used.

Timestamps are stored in the time column. Use comparison operators to compare the value of the time column to a timestamp literal, integer (Unix nanosecond timestamp), or expression.

WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-07-01T00:00:00Z'
WHERE
  time >= 1672531200000000000
  AND time < 1688169600000000000
WHERE
  time >= now() - 1d
  AND time < now()

See Time syntax for information on how to specify alternative time ranges in the WHERE clause.

Regular expressions

Regular expressions can be used to evaluate string values in the WHERE clause using regular expression comparison operators:

  • =~: Matches a regular expression
  • !~: Doesn’t match a regular expression
SELECT * FROM home WHERE room =~ /^K/

For more information about InfluxQL regular expression syntax, see InfluxQL regular expressions.

WHERE clause examples

The following examples use the Get started home sensor sample dataset.

Select data with a specific tag value

Select data from a specific time range

Select data from a relative time range

Select field values above a threshold

Select specific field values

Select field values based on arithmetic

Select data with field values above a threshold and a specific tag value

Select data based on the relationship between columns

Notable behaviors

Single and double quotes

In InfluxQL, single quotation marks (') and double quotation marks (") work differently and can alter the way a WHERE clause functions. Single quotes are used in string and timestamp literals. Double quotes are used to quote identifiers, (time, field, and tag column names).

For example, the following conditional expression compares the value of the location column to the literal string, London:

"location" = 'London'

The following conditional expression compares the value of the location column to the value of the London column:

"location" = "London"

Misused double and single quotes in the WHERE clause often results in unexpected empty query results. For more information about quotation marks, see InfluxQL quotation.

Cannot query multiple time ranges

InfluxDB does not support using OR in the WHERE clause to query multiple time ranges. For example, the following query returns no results:

SELECT *
FROM home
WHERE
  (time >= '2022-01-01T08:00:00Z' AND time <= '2022-01-01T10:00:00Z')
  OR (time >= '2022-01-01T18:00:00Z' AND time <= '2022-01-01T20:00:00Z')

Was this page helpful?

Thank you for your feedback!


Introducing InfluxDB Clustered

A highly available InfluxDB 3.0 cluster on your own infrastructure.

InfluxDB Clustered is a highly available InfluxDB 3.0 cluster built for high write and query workloads on your own infrastructure.

InfluxDB Clustered is currently in limited availability and is only available to a limited group of InfluxData customers. If interested in being part of the limited access group, please contact the InfluxData Sales team.

Learn more
Contact InfluxData Sales

The future of Flux

Flux is going into maintenance mode. You can continue using it as you currently are without any changes to your code.

Flux is going into maintenance mode and will not be supported in InfluxDB 3.0. This was a decision based on the broad demand for SQL and the continued growth and adoption of InfluxQL. We are continuing to support Flux for users in 1.x and 2.x so you can continue using it with no changes to your code. If you are interested in transitioning to InfluxDB 3.0 and want to future-proof your code, we suggest using InfluxQL.

For information about the future of Flux, see the following: