WHERE keyword

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

QuestDB includes a JIT compiler for SQL queries which contain WHERE clauses.

Syntax

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

SELECT ... FROM tableName
WHERE booleanExpression;

Logical operators

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

WHERE [NOT] condition [{ AND | OR } [NOT] condition ...]

Conditions may be grouped using parentheses.

ExampleDemo this query

SELECT * FROM trades
WHERE timestamp IN '$now-1h..$now'
  AND side = 'buy'
  AND (symbol = 'BTC-USDT' OR price > 100000)
LIMIT -3;

Symbol, varchar, and string

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

Exact match

Evaluates match of a symbol, varchar, or string.

WHERE columnName = 'string';

ExampleDemo this query

SELECT * FROM trades
WHERE timestamp IN '$now-1h..$now'
  AND symbol = 'BTC-USDT'
LIMIT -3;

Does NOT match

Evaluates mismatch of a symbol, varchar, or string.

WHERE columnName { != | <> } 'string';

ExampleDemo this query

SELECT * FROM trades
WHERE timestamp IN '$now-1h..$now'
  AND symbol != 'BTC-USDT'
LIMIT -3;

Regular expression match

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

WHERE columnName ~ 'regex';

Regex exampleDemo this query

SELECT * FROM trades
WHERE timestamp IN '$now-1h..$now'
  AND symbol ~ '^BTC'
LIMIT -3;

Regular expression does NOT match

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

WHERE columnName !~ 'regex';

ExampleDemo this query

SELECT * FROM trades
WHERE timestamp IN '$now-1h..$now'
  AND symbol !~ '^BTC'
LIMIT -3;

List search

Evaluates match or mismatch against a list of elements.

WHERE [NOT] columnName IN ('value' [, 'value' ...]);

List matchDemo this query

SELECT * FROM trades
WHERE timestamp IN '$now-1h..$now'
  AND symbol IN ('BTC-USDT', 'ETH-USDT')
LIMIT -20;

List mismatchDemo this query

SELECT * FROM trades
WHERE timestamp IN '$now-1h..$now'
  AND symbol NOT IN ('BTC-USDT', 'ETH-USDT')
LIMIT -20;

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

WHERE columnName { = | != | <> | > | >= | < | <= } value;

Greater than or equalDemo this query

SELECT * FROM trades
WHERE timestamp IN '$now-1h..$now' AND amount >= 1.0
LIMIT -3;

EqualDemo this query

SELECT * FROM trades
WHERE timestamp IN '$now-1h..$now' AND amount = 1.0
LIMIT -3;

Not equalDemo this query

SELECT * FROM trades
WHERE timestamp IN '$now-1h..$now' AND amount != 1.0
LIMIT -3;

Boolean

WHERE [NOT] columnName;

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

The examples below assume a small instruments table:

symbol	is_tradable
BTC-USD	true
ETH-USD	true
XYZ-USD	false

Example - true

SELECT * FROM instruments WHERE is_tradable;

symbol	is_tradable
BTC-USD	true
ETH-USD	true

Example - false

SELECT * FROM instruments WHERE NOT is_tradable;

symbol	is_tradable
XYZ-USD	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.

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 Format	Resulting Timestamp
2026-01-12T12:35:26.123456+01:30	2026-01-12T11:05:26.123456Z
2026-01-12T12:35:26.123456+01	2026-01-12T11:35:26.123456Z
2026-01-12T12:35:26.123456Z	2026-01-12T12:35:26.123456Z
2026-01-12T12:35:26.12345	2026-01-12T12:35:26.123450Z
2026-01-12T12:35:26.1234	2026-01-12T12:35:26.123400Z
2026-01-12T12:35:26.123	2026-01-12T12:35:26.123000Z
2026-01-12T12:35:26.12	2026-01-12T12:35:26.120000Z
2026-01-12T12:35:26.1	2026-01-12T12:35:26.100000Z
2026-01-12T12:35:26	2026-01-12T12:35:26.000000Z
2026-01-12T12:35	2026-01-12T12:35:00.000000Z
2026-01-12T12	2026-01-12T12:00:00.000000Z
2026-01-12	2026-01-12T00:00:00.000000Z
2026-01	2026-01-01T00:00:00.000000Z
2026	2026-01-01T00:00:00.000000Z
2026-01-12 12:35:26.123456-02:00	2026-01-12T14:35:26.123456Z
2026-01-12 12:35:26.123456Z	2026-01-12T12:35:26.123456Z
2026-01-12 12:35:26.123	2026-01-12T12:35:26.123000Z
2026-01-12 12:35:26.12	2026-01-12T12:35:26.120000Z
2026-01-12 12:35:26.1	2026-01-12T12:35:26.100000Z
2026-01-12 12:35:26	2026-01-12T12:35:26.000000Z
2026-01-12 12:35	2026-01-12T12:35:00.000000Z

Exact timestamp

Syntax

WHERE timestampColumn = 'timestamp';

Timestamp equals dateDemo this query

SELECT * FROM trades WHERE timestamp = '2026-04-02T12:00:00.190Z';

Timestamp equals timestampDemo this query

SELECT * FROM trades WHERE timestamp = '2026-04-02T12:00:00.190000Z';

Time range (WHERE IN)

Returns results within a defined range.

Recommended: TICK syntax

For complex timestamp filtering, use TICK interval syntax. TICK handles date ranges, business days, timezones, and schedules in a single expression:

-- Last 5 business days, 9:30 AM New York time, 6.5 hour windows
WHERE ts IN '[$today-5bd..$today-1bd]T09:30@America/New_York#workday;6h30m'

With exchange calendars (Enterprise), you can filter by real exchange schedules including holidays and early closes:

-- NYSE trading hours for January, holidays excluded automatically
WHERE ts IN '2025-01-[01..31]#XNYS'

Syntax

WHERE timestampColumn IN 'partialTimestamp';

partialTimestamp is any prefix of an ISO 8601 timestamp (yyyy, yyyy-MM, yyyy-MM-dd, yyyy-MM-ddThh, yyyy-MM-ddThh:mm, yyyy-MM-ddThh:mm:ss).

Trades in a given yearDemo this query

SELECT * FROM trades WHERE timestamp IN '2026' LIMIT -3;

Trades in a given minuteDemo this query

SELECT * FROM trades WHERE timestamp IN '2026-04-02T12:15' LIMIT -3;

Time range with interval 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

WHERE timestampColumn IN 'timestamp;modifier[;interval;repetition]';

• timestamp is the original time range for the query.

• modifier is a signed integer modifying the upper bound applying to the timestamp:

  • A positive value extends the selected period.
  • A negative value reduces the selected period.

• 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:

Trades in a given year and the first month of the next yearDemo this query

SELECT * FROM trades WHERE timestamp IN '2026;1M' LIMIT -3;

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

Trades in a given month excluding the last 3 daysDemo this query

SELECT * FROM trades WHERE timestamp IN '2026-04;-3d' LIMIT -3;

The range is April 2026. The modifier reduces the upper bound (originally 30 April 2026) by 3 days.

Modifying the interval:

Trades on a given date with an intervalDemo this query

SELECT * FROM trades
WHERE timestamp IN '2025-01-01;1d;1y;2' AND symbol = 'SOL-ETH';

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

A more complete query breakdown would appear as such:

-- IN extension for time-intervals

SELECT * FROM trades WHERE timestamp in '2026'; -- whole year
SELECT * FROM trades WHERE timestamp in '2025-12'; -- whole month
SELECT * FROM trades WHERE timestamp in '2025-12-20'; -- whole day

-- The whole day, extending 15s into the next day
SELECT * FROM trades WHERE timestamp in '2025-12-20;15s';

-- For the past 7 days, 2 seconds before and after midnight
SELECT * from trades WHERE timestamp in '2025-09-20T23:59:58;4s;-1d;7'

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 listDemo this query

SELECT * FROM trades
WHERE timestamp IN ('2026-04-01', '2026-04-01T12:00:00.017999Z', '2026-04-02');

is equivalent to:

IN list equivalent ORDemo this query

SELECT * FROM trades
WHERE timestamp = '2026-04-01'
   OR timestamp = '2026-04-01T12:00:00.017999Z'
   OR timestamp = '2026-04-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.

Explicit rangeDemo this query

SELECT * FROM trades
WHERE timestamp BETWEEN '2026-04-01T00:00:23.000000Z'
                    AND '2026-04-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:

One year before current dateDemo this query

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

Alternatively, TICK interval syntax expresses the same intent more compactly:

Last year using TICK syntaxDemo this query

SELECT * FROM trades
WHERE timestamp IN '$now-1y..$now';

Inclusivity example

Inclusivity is precise, and may be more granular than the provided dates appear.

If a timestamp in the format YYYY-MM-DD is passed forward, it is computed as YYYY-MM-DDThh:mm:ss.sss.

To demonstrate, note the behaviour of the following example queries:

Demonstrating inclusivityDemo this query

SELECT *
FROM trades
WHERE timestamp BETWEEN '2026-04-01' AND '2026-04-03'
LIMIT -1;

symbol	side	price	amount	timestamp
XLM-USDT	buy	0.16304	15.0	2026-04-02T23:59:59.982000Z

The query pushes to the boundaries as far as is possible, all the way to: 2026-04-02T23:59:59.982000Z.

If there was an event at precisely 2026-04-03T00:00:00.00000, it would also be included.

Now let us look at:

Demonstrating inclusivityDemo this query

SELECT *
FROM trades
WHERE timestamp BETWEEN '2026-04-01' AND '2026-04-03T00:00:00.99'
LIMIT -1;

symbol	side	price	amount	timestamp
BTC-USDT	buy	66912.9	0.00054697	2026-04-03T00:00:00.936000Z

Even with fractional seconds, the boundary is inclusive.

A row with timestamp 2026-04-03T00:00:00.990000Z would also return in boundary.