WHERE keyword

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

info

QuestDB includes a JIT compiler for SQL queries which contain WHERE clauses. To find out more about this functionality with details on enabling its use, see the JIT compiler documentation.

Syntax#

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

Flow chart showing the syntax of the WHERE clause

Logical operators#

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

Flow chart showing the detailed syntax of the WHERE clause

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.

Flow chart showing the syntax of the WHERE clause with a string comparison

Example
SELECT * FROM users
WHERE name = 'John';
nameage
John31
John45
......

Does NOT match#

Evaluates mismatch of a string or symbol.

Flow chart showing the syntax of the WHERE clause with a string comparison

Example
SELECT * FROM users
WHERE name != 'John';
nameage
Tim31
Tom45
......

Regular expression match#

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

Flow chart showing the syntax of the WHERE clause with a regex comparison

Regex example
SELECT * FROM users WHERE name ~ 'Jo';
nameage
Joe31
Jonathan45
......

Regular expression does NOT match#

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

Flow chart showing the syntax of the WHERE clause with a regex comparison

Example
SELECT * FROM users WHERE name !~ 'Jo';
nameage
Tim31
Tom45
......

List search#

Evaluates match or mismatch against a list of elements.

Flow chart showing the syntax of the WHERE clause with a list comparison

List match
SELECT * FROM users WHERE name in('Tim', 'Tom');
nameage
Tim31
Tom45
......
List mismatch
SELECT * FROM users WHERE NOT name in('Tim', 'Tom');
nameage
Aaron31
Amelie45
......

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#

Flow chart showing the syntax of the WHERE clause with a numeric 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;

Boolean#

Flow chart showing the syntax of the WHERE clause with a boolean comparison

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;
userIdisActive
12532true
38572true
......
Example - false
SELECT * FROM users WHERE NOT isActive;
userIdisActive
876534false
43234false
......

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:

Valid STRING FormatResulting Timestamp
2010-01-12T12:35:26.123456+01:302010-01-12T11:05:26.123456Z
2010-01-12T12:35:26.123456+012010-01-12T11:35:26.123456Z
2010-01-12T12:35:26.123456Z2010-01-12T12:35:26.123456Z
2010-01-12T12:35:26.123452010-01-12T12:35:26.123450Z
2010-01-12T12:35:26.12342010-01-12T12:35:26.123400Z
2010-01-12T12:35:26.1232010-01-12T12:35:26.123000Z
2010-01-12T12:35:26.122010-01-12T12:35:26.120000Z
2010-01-12T12:35:26.12010-01-12T12:35:26.100000Z
2010-01-12T12:35:262010-01-12T12:35:26.000000Z
2010-01-12T12:352010-01-12T12:35:00.000000Z
2010-01-12T122010-01-12T12:00:00.000000Z
2010-01-122010-01-12T00:00:00.000000Z
2010-012010-01-01T00:00:00.000000Z
20102010-01-01T00:00:00.000000Z
2010-01-12 12:35:26.123456-02:002010-01-12T14:35:26.123456Z
2010-01-12 12:35:26.123456Z2010-01-12T12:35:26.123456Z
2010-01-12 12:35:26.1232010-01-12T12:35:26.123000Z
2010-01-12 12:35:26.122010-01-12T12:35:26.120000Z
2010-01-12 12:35:26.12010-01-12T12:35:26.100000Z
2010-01-12 12:35:262010-01-12T12:35:26.000000Z
2010-01-12 12:352010-01-12T12:35:00.000000Z

Exact timestamp#

Syntax#

Flow chart showing the syntax of the WHERE clause with a timestamp comparison

Timestamp equals date
SELECT scores WHERE ts = '2010-01-12T00:02:26.000Z';
tsscore
2010-01-12T00:02:26.000Z2.4
2010-01-12T00:02:26.000Z3.1
......
Timestamp equals timestamp
SELECT scores WHERE ts = '2010-01-12T00:02:26.000000Z';
tsscore
2010-01-12T00:02:26.000000Z2.4
2010-01-12T00:02:26.000000Z3.1
......

Time range#

Returns results within a defined range.

Syntax#

Flow chart showing the syntax of the WHERE clause with a partial timestamp comparison

Results in a given year
SELECT * FROM scores WHERE ts IN '2018';
tsscore
2018-01-01T00:0000.000000Z123.4
......
2018-12-31T23:59:59.999999Z115.8
Results in a given minute
SELECT * FROM scores WHERE ts IN '2018-05-23T12:15';
tsscore
2018-05-23T12:15:00.000000Z123.4
......
2018-05-23T12:15:59.999999Z115.8

Time range with modifier#

You can apply a modifier to further customize the range. The modifier extends the upper bound of the original timestamp based on the modifier parameter. An optional interval with occurrence can be set, to apply the search in the given time range repeatedly, for a set number of times.

Syntax#

Flow chart showing the syntax of the WHERE clause with a timestamp/modifier comparison

  • timestamp is the original time range for the query.

  • modifier is an unsigned integer modifying the upper bound applying to the timestamp.

  • interval is an unsigned integer indicating the desired interval period for the time range.

  • repetition is an unsigned integer indicating the number of times the interval should be applied.

Examples#

Modifying the range:

Results in a given year and the first month of the next year
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.

tsscore
2018-01-01T00:00:00.000000Z123.4
......
2019-01-31T23:59:59.999999Z115.8

Modifying the interval:

Results on a given date with an interval
SELECT * FROM scores WHERE ts IN '2018-01-01;1d;1y;2';

The range is extended by one day from Jan 1 2018, with a one-year interval, repeated twice. This means that the query searches for results on Jan 1-2 in 2018 and in 2019:

tsscore
2018-01-01T00:00:00.000000Z123.4
......
2018-01-02T23:59:59.999999Z110.3
2019-01-01T00:00:00.000000Z128.7
......
2019-01-02T23:59:59.999999Z103.8

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:

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

is equivalent to:

IN list equivalent OR
SELECT * FROM scores
WHERE ts = '2018-01-01' or ts = '2018-01-01T12:00' or ts = '2018-01-02');
tsvalue
2018-01-01T00:00:00.000000Z123.4
2018-01-01T12:00:00.000000Z589.1
2018-01-02T00:00:00.000000Z131.5

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.

Explicit range
SELECT * FROM scores
WHERE ts BETWEEN '2018-01-01T00:00:23.000000Z' AND '2018-01-01T00:00:23.500000Z';
tsvalue
2018-01-01T00:00:23.000000Z123.4
......
2018-01-01T00:00:23.500000Z131.5

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

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