WHERE clause filters data. Filter expressions are required to return boolean result.

Syntax

The general syntax is as follows. Specific filters have distinct syntaxes detailed thereafter.

Logical operators

QuestDB supports AND, OR, NOT as logical operators and can assemble conditions using brackets ().

SELECT * FROM table
WHERE
a = 1 AND (b = 2 OR c = 3 AND NOT d);

Symbol and string

QuestDB can filter strings and symbols based on equality, inequality, and regular expression patterns.

Exact match

Evaluates match of a string or symbol.

SELECT * FROM users
WHERE name = 'John';

Does NOT match

Evaluates mismatch of a string or symbol.

SELECT * FROM users
WHERE name != 'John';

Regular expression match

Evaluates match against a regular expression defined using java.util.regex patterns.

SELECT * FROM users WHERE ~=(name, 'Jo');

Regular expression does NOT match

Evaluates mismatch against a regular expression defined using java.util.regex patterns.

SELECT * FROM users WHERE !~(name, 'Jo');

List search

Evaluates match or mismatch against a list of elements.

SELECT * FROM users WHERE name in('Tim', 'Tom');

SELECT * FROM users WHERE NOT name in('Tim', 'Tom');

Numeric

QuestDB can filter numeric values based on equality, inequality, comparison, and proximity

:::note

For timestamp filters, we recommend the timestamp search notation which is faster and less verbose.

:::

Equality, inequality and comparison

SELECT * FROM users WHERE age >= 23;

SELECT * FROM users WHERE age = 23;

SELECT * FROM users WHERE age != 23;

Proximity

Evaluates whether the column value is within a range of the target value. This is useful to simulate equality on double and float values.

SELECT * FROM users WHERE eq(age, 23, 0.00001);

:::tip

When performing multiple equality checks of double values against integer constants, it may be preferable to store double values as long integers with a scaling factor.

:::

Boolean

Using the columnName will return true values. To return false values, precede the column name with the NOT operator.

SELECT * FROM users WHERE isActive;

SELECT * FROM users WHERE NOT isActive;

Timestamp and date

QuestDB supports both its own timestamp search notation and standard search based on inequality. This section describes the use of the timestamp search notation which is efficient and fast but requires a designated timestamp. Remember, designated timestamp can be applied dynamically.

Exact timestamp

Syntax

SELECT scores WHERE ts = '2010-01-12T00:02:26.000Z';

SELECT scores WHERE ts = '2010-01-12T00:02:26.000000Z';

Time range

Return results within a defined range

Syntax

SELECT * FROM scores WHERE ts = '2018';

SELECT * FROM scores WHERE ts = '2018-05-23T12:15';

Time range with modifier

You can apply a modifier to further customise the range. The algorithm will calculate the resulting range by modifying the upper bound of the original range by the modifier parameter.

Syntax

multiplier is a signed integer.

• A positive value extends the interval.
• A negative value reduces the interval.

SELECT * FROM scores WHERE ts = '2018;1M';

The range is 2018. The modifier extends the upper bound (originally 31 Dec 2018) by one month.

SELECT * FROM scores WHERE ts = '2018-01;-3d';

The range is Jan 2018. The modifier reduces the upper bound (originally 31 Dec 2018) by 3 days.

Explicit range

Syntax

For non-standard ranges, users can explicitly specify the target range using the in operator.

lower_bound and upper_bound must be valid timestamps or dates and are inclusive.

SELECT * FROM scores
WHERE ts in('2018-01-01T00:00:23.000000Z' , '2018-01-01T00:00:23.500000Z');