docs-v2/where.md at monolith - docs-v2 - Gitea: ArmstrongLabs

11 KiB

Raw Permalink Blame History

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

Syntax
Operators
- Comparison operators
- Logical operators
Time ranges
Regular expressions
WHERE clause examples
Notable behaviors
- Single and double quotes
- Cannot query multiple time ranges

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.

{{< code-tabs-wrapper >}} {{% code-tabs %}} Timestamp Integer Expression {{% /code-tabs %}} {{% code-tab-content %}}

WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-07-01T00:00:00Z'

{{% /code-tab-content %}} {{% code-tab-content %}}

WHERE
  time >= 1672531200000000000
  AND time < 1688169600000000000

{{% /code-tab-content %}} {{% code-tab-content %}}

WHERE
  time >= now() - 1d
  AND time < now()

{{% /code-tab-content %}} {{< /code-tabs-wrapper >}}

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

[!Important] InfluxQL does not support querying multiple time ranges.

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.

{{< expand-wrapper >}} {{% expand "Select data with a specific tag value" %}}

SELECT * FROM home WHERE room = 'Living Room'

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% influxdb/custom-timestamps %}}

time	co	hum	room	temp
2022-01-01T08:00:00Z	0	35.9	Living Room	21.1
2022-01-01T09:00:00Z	0	35.9	Living Room	21.4
2022-01-01T10:00:00Z	0	36	Living Room	21.8
2022-01-01T11:00:00Z	0	36	Living Room	22.2
2022-01-01T12:00:00Z	0	35.9	Living Room	22.2
...	...	...	...	...

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{% expand "Select data from a specific time range" %}}

{{% influxdb/custom-timestamps %}}

SELECT *
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T10:00:00Z'

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time	co	hum	room	temp
2022-01-01T08:00:00Z	0	35.9	Kitchen	21
2022-01-01T08:00:00Z	0	35.9	Living Room	21.1
2022-01-01T09:00:00Z	0	36.2	Kitchen	23
2022-01-01T09:00:00Z	0	35.9	Living Room	21.4
2022-01-01T10:00:00Z	0	36.1	Kitchen	22.7
2022-01-01T10:00:00Z	0	36	Living Room	21.8

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{% expand "Select data from a relative time range" %}}

{{% influxdb/custom-timestamps %}}

SELECT * FROM home WHERE time >= '2022-01-01T20:00:00Z' - 2h

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time	co	hum	room	temp
2022-01-01T18:00:00Z	18	36.9	Kitchen	23.3
2022-01-01T18:00:00Z	9	36.2	Living Room	22.8
2022-01-01T19:00:00Z	22	36.6	Kitchen	23.1
2022-01-01T19:00:00Z	14	36.3	Living Room	22.5
2022-01-01T20:00:00Z	26	36.5	Kitchen	22.7
2022-01-01T20:00:00Z	17	36.4	Living Room	22.2

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{% expand "Select field values above a threshold" %}}

SELECT co FROM home WHERE co > 9

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% influxdb/custom-timestamps %}}

time	co
2022-01-01T18:00:00Z	18
2022-01-01T19:00:00Z	14
2022-01-01T19:00:00Z	22
2022-01-01T20:00:00Z	17
2022-01-01T20:00:00Z	26

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{% expand "Select specific field values" %}}

SELECT room, co FROM home WHERE co = 9

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% influxdb/custom-timestamps %}}

time	room	co
2022-01-01T17:00:00Z	Kitchen	9
2022-01-01T18:00:00Z	Living Room	9

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{% expand "Select field values based on arithmetic" %}}

SELECT room, co FROM home WHERE co - 10 > 5

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% influxdb/custom-timestamps %}}

time	room	co
2022-01-01T18:00:00Z	Kitchen	18
2022-01-01T19:00:00Z	Kitchen	22
2022-01-01T20:00:00Z	Living Room	17
2022-01-01T20:00:00Z	Kitchen	26

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{% expand "Select data with field values above a threshold and a specific tag value" %}}

SELECT * FROM home WHERE temp > 22.7 AND room = 'Kitchen'

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% influxdb/custom-timestamps %}}

time	co	hum	room	temp
2022-01-01T09:00:00Z	0	36.2	Kitchen	23
2022-01-01T13:00:00Z	1	36.5	Kitchen	22.8
2022-01-01T14:00:00Z	1	36.3	Kitchen	22.8
2022-01-01T18:00:00Z	18	36.9	Kitchen	23.3
2022-01-01T19:00:00Z	22	36.6	Kitchen	23.1

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{% expand "Select data based on the relationship between columns" %}}

SELECT co, temp FROM home WHERE co > temp

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% influxdb/custom-timestamps %}}

time	co	temp
2022-01-01T20:00:00Z	26	22.7

{{% /influxdb/custom-timestamps %}} {{% /expand %}} {{< /expand-wrapper >}}

Notable behaviors

Single and double quotes
Cannot query multiple time ranges

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:

{{% influxdb/custom-timestamps %}}

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')

{{% /influxdb/custom-timestamps %}}