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.

If a table does not have a designated timestamp applied during table creation, one may be applied dynamically during a select operation.

Native timestamp format

QuestDB automatically recognizes strings formatted as ISO timestamp as a timestamp type. The following are valid examples of strings parsed as timestamp types:

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 IN '2018';

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

Time range with modifier

You can apply a modifier to further customize 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 IN '2018;1M';

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

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

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

IN with multiple arguments

Syntax

IN with more than 1 argument is treated as standard SQL IN. It is a shorthand of multiple OR conditions, i.e. the following query:

SELECT * FROM scores
WHERE ts IN ('2018-01-01', '2018-01-01T12:00', '2018-01-02');

is equivalent to:

SELECT * FROM scores
WHERE ts = '2018-01-01' or ts = '2018-01-01T12:00' or ts = '2018-01-02');

BETWEEN

Syntax

For non-standard ranges, users can explicitly specify the target range using the BETWEEN operator. As with standard SQL, both upper and lower bounds of BETWEEN are inclusive, and the order of lower and upper bounds is not important so that BETWEEN X AND Y is equivalent to BETWEEN Y AND X.

SELECT * FROM scores
WHERE ts BETWEEN '2018-01-01T00:00:23.000000Z' AND '2018-01-01T00:00:23.500000Z';

BETWEEN can accept non-constant bounds, for example, the following query will return all records older than one year before the current date:

SELECT * FROM scores
WHERE ts BETWEEN to_str(now(), 'yyyy-MM-dd')
AND dateadd('y', -1, to_str(now(), 'yyyy-MM-dd'));