Date and Time Operators

This page covers operators for filtering data by timestamp in WHERE clauses.

Recommended: TICK syntax

For most timestamp filtering, use IN with TICK syntax. It handles simple ranges, multiple intervals, business days, timezones, and more in a single unified syntax:

WHERE ts IN '2024-01-[01..31]T09:30@EST#wd;6h30m'

The interval() function and BETWEEN operator described below are alternatives for specific use cases, but TICK syntax covers most needs.

For date/time manipulation functions (dateadd(), now(), extract(), etc.), see Date/time functions.

IN with timestamp intervals

The IN operator with a string argument queries timestamp intervals. QuestDB uses TICK syntax for all timestamp interval expressions.

-- Simple: all data from a specific day
SELECT * FROM trades WHERE ts IN '2024-01-15';

-- With duration: 1-hour window starting at 09:30
SELECT * FROM trades WHERE ts IN '2024-01-15T09:30;1h';

-- Multiple dates with bracket expansion
SELECT * FROM trades WHERE ts IN '2024-01-[15,16,17]';

-- Workdays only with timezone
SELECT * FROM trades WHERE ts IN '2024-01-[01..31]T09:30@EST#wd;6h30m';

-- Dynamic: last 5 business days
SELECT * FROM trades WHERE ts IN '[$today-5bd..$today-1bd]';

For complete documentation of all patterns including bracket expansion, date variables, timezones, and day filters, see TICK interval syntax.

Interval scan optimization

When timestamp predicates are used on a designated timestamp column, QuestDB performs an interval scan using binary search instead of a full table scan.

This optimization works with:

• IN with TICK syntax or interval() function
• BETWEEN ranges
• Comparison operators (>, <, >=, <=)
• AND combinations (intersects intervals)
• OR combinations (unions intervals)

-- AND: intersects intervals (both conditions must match)
WHERE ts IN '2024-01' AND ts > '2024-01-15'
-- Results in: 2024-01-15 to 2024-01-31

-- OR: unions intervals (either condition matches)
WHERE ts IN '2024-01-10' OR ts IN '2024-01-20'
-- Results in: two separate interval scans

IN with interval() function

The interval() function creates an interval from two explicit bounds. This is useful when bounds come from variables or subqueries.

tip

For static bounds, prefer TICK syntax: IN '2024-01-01;30d' instead of IN interval('2024-01-01', '2024-01-31').

Interval from explicit bounds

SELECT * FROM trades
WHERE ts IN interval('2024-01-01', '2024-01-31');

Interval with bound parameters (prepared statements)

SELECT * FROM trades
WHERE ts IN interval($1, $2);

BETWEEN ... AND

The BETWEEN operator specifies an inclusive range. Useful when working with dynamic bounds from functions.

tip

For static ranges, prefer TICK syntax: IN '2024-01' instead of BETWEEN '2024-01-01' AND '2024-01-31'.

Explicit timestamp range

SELECT * FROM trades
WHERE ts BETWEEN '2024-01-01T00:00:00Z' AND '2024-01-31T23:59:59Z';

Dynamic range using functions

SELECT * FROM trades
WHERE ts BETWEEN dateadd('d', -7, now()) AND now();

BETWEEN produces the same interval scan optimization as IN when used on a designated timestamp column.

When to use each

Use case	Recommended
Any static range	IN with TICK — '2024-01', '2024-01-15T09:00;1h'
Multiple intervals	IN with TICK — '2024-01-[15,16,17]'
Schedules, business days	IN with TICK — '[$today-5bd..$today]#workday'
Dynamic bounds from functions	BETWEEN — BETWEEN dateadd('d', -7, now()) AND now()
Prepared statement parameters	IN interval() — IN interval($1, $2)

See also

• TICK interval syntax — Full reference for IN patterns
• Interval scan — How timestamp queries are optimized
• Designated timestamp — Required for interval scan
• Date/time functions — dateadd(), now(), etc.