WHERE keyword

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 ().

Example

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.

Example

SELECT * FROM users

WHERE name = 'John';

name	age
John	31
John	45
...	...

Does NOT match

Evaluates mismatch of a string or symbol.

Example

SELECT * FROM users

WHERE name != 'John';

name	age
Tim	31
Tom	45
...	...

Regular expression match

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

Example

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

name	age
Joe	31
Jonathan	45
...	...

Regular expression does NOT match

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

Example

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

name	age
Tim	31
Tom	45
...	...

List search

Evaluates match or mismatch against a list of elements.

List match

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

name	age
Tim	31
Tom	45
...	...

List mismatch

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

name	age
Aaron	31
Amelie	45
...	...

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

Superior or equal to 23

SELECT * FROM users WHERE age >= 23;

Equal to 23

SELECT * FROM users WHERE age = 23;

NOT Equal to 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.

Equal to 23 with 0.00001 precision

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.

Example - true

SELECT * FROM users WHERE isActive;

userId	isActive
12532	true
38572	true
...	...

Example - false

SELECT * FROM users WHERE NOT isActive;

userId	isActive
876534	false
43234	false
...	...

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

Example - Date

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

timestamp	score
2010-01-12T00:02:26.000Z	2.4
2010-01-12T00:02:26.000Z	3.1
...	...

Example - Timestamp

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

timestamp	score
2010-01-12T00:02:26.000000Z	2.4
2010-01-12T00:02:26.000000Z	3.1
...	...

Time range

Return results within a defined range

Syntax

Results in a given year

SELECT * FROM scores WHERE ts = '2018';

timestamp	score
2018-01-01T00:0000.000000Z	123.4
...	...
2018-12-31T23:59:59.999999Z	115.8

Results in a given minute

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

timestamp	score
2018-05-23T12:15:00.000000Z	123.4
...	...
2018-05-23T12:15:59.999999Z	115.8

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.

Results in a given year and the first month of the next year

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

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

timestamp	score
2018-01-01T00:00:00.000000Z	123.4
...	...
2019-01-31T23:59:59.999999Z	115.8

Results in a given month excluding the last 3 days

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.

timestamp	score
2018-01-01T00:00:00.000000Z	123.4
...	...
2019-01-28T23:59:59.999999Z	115.8

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.

Explicit range

SELECT * FROM scores

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

timestamp	value
2018-01-01T00:00:23.000000Z	123.4
...	...
2018-01-01T00:00:23.500000Z	131.5