TICK interval syntax

TICK (Temporal Interval Calendar Kit) is a syntax for expressing complex temporal intervals in a single string. Use it with the IN operator to query multiple time ranges, schedules, and patterns efficiently.

-- NYSE trading hours on workdays for January
SELECT * FROM trades
WHERE ts IN '2024-01-[01..31]T09:30@America/New_York#workday;6h30m';

This single expression generates interval scans for every weekday in January, each starting at 9:30 AM New York time and lasting 6 hours 30 minutes.

Key Points

TICK = declarative syntax for complex time intervals in WHERE ts IN '...'
Syntax order: date [T time] @timezone #dayFilter ;duration
Each generated interval uses optimized interval scan (binary search)
Use [a,b,c] for values, [a..b] for ranges, #workday for day filters
Overlapping intervals are automatically merged

Grammar summary

TICK_EXPR     = DATE_PART [TIME] [TIMEZONE] [FILTER] [DURATION]

DATE_PART     = literal_date                    -- '2024-01-15'
              | date_list                       -- '[2024-01-15, 2024-03-20]'
              | date_variable                   -- '$today', '$now - 2h'
              | bracket_expansion               -- '2024-01-[10..15]'
              | iso_week                        -- '2024-W01-1'

TIME          = 'T' time_value                  -- 'T09:30'
              | 'T' bracket_expansion           -- 'T[09:00,14:30]'

TIMEZONE      = '@' iana_name                   -- '@America/New_York'
              | '@' offset                      -- '@+02:00', '@UTC'

FILTER        = '#workday' | '#weekend'         -- business day filters
              | '#' day_list                    -- '#Mon,Wed,Fri'

DURATION      = ';' duration_value              -- ';6h30m'

-- Bracket expansion: generates multiple values from a single field
bracket_expansion = '[' expansion_item (',' expansion_item)* ']'
expansion_item    = value                       -- single: [10]
                  | value '..' value            -- range:  [10..15]
                  -- mixed example: [5,10..12,20] = 5, 10, 11, 12, 20

-- Date list: multiple complete dates (can nest bracket expansions)
date_list    = '[' date_entry (',' date_entry)* ']'
date_entry   = literal_date                     -- '2024-01-15'
             | date_variable                    -- '$today'
             | literal_date with brackets       -- '2024-01-[01..05]'

-- Date variable with optional arithmetic and ranges
date_variable = '$today' | '$yesterday' | '$tomorrow' | '$now'
              | date_variable ('+' | '-') amount unit
              | date_variable '..' date_variable   -- '$now - 2h..$now'

unit          = 'y' | 'M' | 'w' | 'd' | 'bd' | 'h' | 'm' | 's' | 'T' | 'u' | 'n'
              --  ↑    ↑         ↑    ↑
              -- 'bd' (business days) valid only in date arithmetic, not duration

Why TICK

Traditional approaches to complex time queries require:

Multiple UNION ALL statements
Application-side date generation
Complex BETWEEN logic with many OR clauses

TICK replaces all of these with a declarative syntax that generates multiple optimized interval scans from a single expression.

Use TICK when:

Querying relative time windows ($now - 1h..$now, $today)
Building rolling windows with business day calculations
Working with schedules (workdays, weekends, specific days)
Needing timezone-aware time windows with DST handling
Querying multiple non-contiguous dates or time windows

Use simple IN or BETWEEN when:

Single continuous time range with absolute dates (WHERE ts IN '2024-01-15')
Simple date/time literals without patterns or variables

Quick start

Common patterns to get started:

-- Last hour of data
WHERE ts IN '$now - 1h..$now'

-- Last 30 minutes
WHERE ts IN '$now - 30m..$now'

-- Today's data (full day)
WHERE ts IN '$today'

-- Last 5 business days
WHERE ts IN '$today - 5bd..$today - 1bd'

-- Workdays only with time window
WHERE ts IN '2024-01-[01..31]T09:00#workday;8h'

-- Multiple times on one day
WHERE ts IN '2024-01-15T[09:00,12:00,18:00];1h'

-- With timezone
WHERE ts IN '2024-01-15T09:30@America/New_York;6h30m'

Syntax order

Components must appear in this order:

date [T time] @ timezone # dayFilter ; duration
 │       │         │          │           │
 │       │         │          │           └─ interval length (e.g., ;6h30m)
 │       │         │          └─ day filter (e.g., #workday)
 │       │         └─ timezone (e.g., @America/New_York)
 │       └─ time component (e.g., T09:30)
 └─ date with optional brackets (e.g., 2024-01-[01..31])

Examples showing the order:

Expression	Components used
`'2024-01-15'`	date only
`'2024-01-15T09:30'`	date + time
`'2024-01-15T09:30@UTC'`	date + time + timezone
`'2024-01-15T09:30#workday'`	date + time + filter
`'2024-01-15T09:30;1h'`	date + time + duration
`'2024-01-15T09:30@America/New_York#workday;6h30m'`	all components

Quick reference

Feature	Syntax	Example
Bracket expansion	`[a,b,c]`	`'2024-01-[10,15,20]'`
Range expansion	`[a..b]`	`'2024-01-[10..15]'`
Date list	`[date1,date2]`	`'[2024-01-15,2024-03-20]'`
Time list	`T[time1,time2]`	`'2024-01-15T[09:00,14:30]'`
Timezone	`@timezone`	`'T09:00@America/New_York'`
Day filter	`#filter`	`'#workday'`, `'#Mon,Wed,Fri'`
Duration	`;duration`	`';6h30m'`, `';1h'`
ISO week	`YYYY-Www-D`	`'2024-W01-1'`
Date variable	`$var`	`'$today'`, `'$now - 2h'`
Date arithmetic	`$var ± Nu`	`'$today+5bd'`, `'$now-30m'`, `'$today+1M'`
Variable range	`$start..$end`	`'$now-2h..$now'`, `'$today..$today+5d'`

Interval behavior

Whitespace

Whitespace is flexible in TICK expressions:

-- Inside brackets - spaces are ignored:
'2024-01-[10,15,20]'
'2024-01-[ 10 , 15 , 20 ]'

-- Around arithmetic operators - spaces are optional:
'$now - 2h..$now'
'$now-2h..$now'

Interval merging

When expanded intervals overlap, they are automatically merged:

'2024-01-15T[09:00,10:30];2h'
-- 09:00-11:00 overlaps with 10:30-12:30
-- Result: single merged interval 09:00-12:30

This ensures efficient query execution without duplicate scans.

Optional brackets for date variables

Single date variables can omit brackets, even with suffixes:

-- Single variable - brackets optional:
WHERE ts IN '$today'
WHERE ts IN '$now;1h'
WHERE ts IN '$todayT09:30'
WHERE ts IN '$today@Europe/London'

Ranges can also omit brackets when used alone:

-- Range without suffixes - brackets optional:
WHERE ts IN '$now - 2h..$now'
WHERE ts IN '$today..$today + 5d'

Brackets are required for:

-- Ranges with suffixes - brackets required:
WHERE ts IN '[$now - 2h..$now]@America/New_York'
WHERE ts IN '[$today..$today + 5d]#workday;8h'

-- Lists - brackets required:
WHERE ts IN '[$today, $yesterday, 2024-01-15]'

Date variables

Use dynamic date references that resolve at query time:

Variable	Description	Interval type	Example value (Jan 22, 2026 at 14:35:22)
`$today`	Current day	Full day	`2026-01-22T00:00:00` to `2026-01-22T23:59:59.999999`
`$yesterday`	Previous day	Full day	`2026-01-21T00:00:00` to `2026-01-21T23:59:59.999999`
`$tomorrow`	Next day	Full day	`2026-01-23T00:00:00` to `2026-01-23T23:59:59.999999`
`$now`	Current timestamp	Point-in-time	`2026-01-22T14:35:22.123456` (exact moment)

Interval vs point-in-time

$today, $yesterday, $tomorrow produce full day intervals (midnight to midnight)
$now produces a point-in-time (exact moment with microsecond precision)

Without a duration suffix, $now matches only the exact microsecond. Add a duration or use a range to create a useful window:

-- Point-in-time: matches only the exact microsecond (rarely useful alone)
WHERE ts IN '$now'

-- 1-hour window starting at current moment (extends forward)
WHERE ts IN '$now;1h'

-- Last 2 hours (from 2h ago until now)
WHERE ts IN '$now - 2h..$now'

Variables are case-insensitive: $TODAY, $Today, and $today are equivalent.

Date arithmetic

Add or subtract time from date variables using any time unit. All units except bd (business days) work in both duration and arithmetic contexts.

-- Calendar day arithmetic
'$today + 5d'      -- 5 days from today
'$today - 3d'      -- 3 days ago

-- Business day arithmetic (skips weekends) - arithmetic only
'$today + 1bd'     -- next business day
'$today - 5bd'     -- 5 business days ago

-- Hour/minute/second arithmetic (typically with $now)
'$now - 2h'        -- 2 hours ago
'$now - 30m'       -- 30 minutes ago
'$now - 90s'       -- 90 seconds ago

-- Sub-second precision
'$now - 500T'      -- 500 milliseconds ago
'$now - 100u'      -- 100 microseconds ago

-- Calendar-aware units (handle varying month lengths, leap years)
'$today + 1M'      -- same day next month
'$today + 1y'      -- same day next year
'$today + 2w'      -- 2 weeks from today

Date variable ranges

Generate multiple intervals from start to end:

-- Next 5 calendar days
'$today..$today + 5d'

-- Next 5 business days (weekdays only)
'$today..$today + 5bd'

-- Last work week
'$today - 5bd..$today - 1bd'

-- Last 2 hours
'$now - 2h..$now'

-- Last 30 minutes
'$now - 30m..$now'

-- Next 3 months
'$today..$today + 3M'

Ranges vs durations

Ranges ($start..$end) create a single continuous interval from start to end:

-- Single interval: from 2 hours ago until now
'$now - 2h..$now'

-- Single interval: from 3 days ago until today (end of day)
'$today - 3d..$today'

Durations (;Nh) extend from a point by the specified amount:

-- Single interval: starting at $now, lasting 2 hours forward
'$now;2h'

For multiple discrete intervals, use a list with duration:

-- Three separate 1-hour intervals
'[$now - 3h, $now - 2h, $now - 1h];1h'

Mixed date lists

Combine variables with static dates (brackets required for lists):

-- Today, yesterday, and a specific date
SELECT * FROM trades WHERE ts IN '[$today, $yesterday, 2024-01-15]';

-- Compare today vs same day last week
SELECT * FROM trades WHERE ts IN '[$today, $today - 7d]T09:30;6h30m';

-- Hourly windows starting 4 hours ago
SELECT * FROM trades WHERE ts IN '[$now - 4h, $now - 3h, $now - 2h, $now - 1h, $now]';

Bracket expansion

Brackets expand a single field into multiple values:

-- Days 10, 15, and 20 of January
SELECT * FROM trades WHERE ts IN '2024-01-[10,15,20]';

-- Days 10 through 15 (inclusive range)
SELECT * FROM trades WHERE ts IN '2024-01-[10..15]';

-- Mixed: specific values and ranges
SELECT * FROM trades WHERE ts IN '2024-01-[5,10..12,20]';

Multiple brackets (Cartesian product)

Multiple bracket groups produce all combinations:

-- January and June, 10th and 15th = 4 intervals
SELECT * FROM trades WHERE ts IN '2024-[01,06]-[10,15]';
-- Expands to: 2024-01-10, 2024-01-15, 2024-06-10, 2024-06-15

Bracket positions

Brackets work in any numeric field:

Field	Example	Result
Month	`'2024-[01,06]-15'`	Jan 15, Jun 15
Day	`'2024-01-[10,15]'`	10th, 15th
Hour	`'2024-01-10T[09,14]:30'`	09:30, 14:30
Minute	`'2024-01-10T10:[00,30]'`	10:00, 10:30

Date lists

Start with [ for non-contiguous dates:

-- Specific dates
SELECT * FROM trades WHERE ts IN '[2024-01-15,2024-03-20,2024-06-01]';

-- With nested bracket expansion
SELECT * FROM trades WHERE ts IN '[2024-12-31,2025-01-[01..05]]';
-- Expands to: Dec 31, Jan 1, Jan 2, Jan 3, Jan 4, Jan 5

Date lists with time suffix

-- 09:30 on specific dates
SELECT * FROM trades WHERE ts IN '[2024-01-15,2024-01-20]T09:30';

-- Trading hours on specific dates
SELECT * FROM trades WHERE ts IN '[2024-01-15,2024-01-20]T09:30;6h30m';

Time lists

Specify multiple complete times with colons inside brackets:

-- Morning and evening sessions
SELECT * FROM trades WHERE ts IN '2024-01-15T[09:00,18:00];1h';

-- Three daily check-ins
SELECT * FROM metrics WHERE ts IN '2024-01-15T[08:00,12:00,18:00];30m';

Time list vs numeric expansion

The presence of : inside the bracket determines the mode:

Syntax	Mode	Expands to
`T[09,14]:30`	Numeric expansion (hour field)	09:30 and 14:30
`T[09:00,14:30]`	Time list (complete times)	09:00 and 14:30

Use numeric expansion when times share the same minutes (e.g., both at :30). Use time lists when times differ completely (e.g., 09:00 and 14:30).

Timezone support

Add @timezone after the time component:

-- 09:30 in New York time (automatically handles DST)
SELECT * FROM trades WHERE ts IN '2024-01-15T09:30@America/New_York';

-- Numeric offset
SELECT * FROM trades WHERE ts IN '2024-01-15T09:30@+02:00';

-- UTC
SELECT * FROM trades WHERE ts IN '2024-01-15T09:30@UTC';

Supported timezone formats

Format	Example
IANA name	`@America/New_York`, `@Europe/London`
Offset	`@+03:00`, `@-05:00`
Compact offset	`@+0300`, `@-0500`
Hour-only	`@+03`, `@-05`
UTC/GMT	`@UTC`, `@GMT`, `@Z`

Per-element timezones

Each date or time can have its own timezone:

-- Market opens in different cities
SELECT * FROM trades
WHERE ts IN '2024-01-15T[09:30@America/New_York,08:00@Europe/London,09:00@Asia/Tokyo];6h';

-- Per-date timezone (comparing same local time in winter vs summer)
SELECT * FROM trades
WHERE ts IN '[2024-01-15@Europe/London,2024-07-15@Europe/London]T08:00';

Day-of-week filter

Add #filter to include only specific days:

-- Workdays only (Monday-Friday)
SELECT * FROM trades WHERE ts IN '2024-01-[01..31]#workday';

-- Weekends only
SELECT * FROM logs WHERE ts IN '2024-01-[01..31]T02:00#weekend;4h';

-- Specific days
SELECT * FROM attendance WHERE ts IN '2024-01-[01..31]#Mon,Wed,Fri';

Available filters

Filter	Days included
`#workday` or `#wd`	Monday - Friday
`#weekend`	Saturday, Sunday
`#Mon`, `#Tue`, etc.	Specific day
`#Mon,Wed,Fri`	Multiple days

Day names are case-insensitive. Both #Mon and #Monday work.

Filter with timezone

The filter applies to local time before timezone conversion:

-- 09:30 New York time, workdays only
-- "Monday" means Monday in New York, not Monday in UTC
SELECT * FROM trades
WHERE ts IN '2024-01-[01..31]T09:30@America/New_York#workday;6h30m';

Duration suffix

Add ;duration to specify interval length:

-- 1-hour intervals
SELECT * FROM trades WHERE ts IN '2024-01-15T09:00;1h';

-- 6 hours 30 minutes (NYSE trading day)
SELECT * FROM trades WHERE ts IN '2024-01-15T09:30;6h30m';

-- Precise sub-second duration
SELECT * FROM hft_data WHERE ts IN '2024-01-15T09:30:00;1s500T';

Time units

Unit	Name	Description	Duration	Arithmetic
`y`	Years	Calendar years (handles leap years)	Yes	Yes
`M`	Months	Calendar months (handles varying lengths)	Yes	Yes
`w`	Weeks	7 days	Yes	Yes
`d`	Days	24 hours	Yes	Yes
`bd`	Business days	Weekdays only (skips Sat/Sun)	No	Yes
`h`	Hours	60 minutes	Yes	Yes
`m`	Minutes	60 seconds	Yes	Yes
`s`	Seconds	1,000 milliseconds	Yes	Yes
`T`	Milliseconds	1,000 microseconds	Yes	Yes
`u`	Microseconds	1,000 nanoseconds	Yes	Yes
`n`	Nanoseconds	Base unit	Yes	Yes

Units are case-sensitive: M = months, m = minutes, T = milliseconds. The d unit also accepts uppercase D for backward compatibility.

Multi-unit durations

Combine units for precise specifications:

-- 2 hours, 15 minutes, 30 seconds
';2h15m30s'

-- 500 milliseconds + 250 microseconds
';500T250u'

-- NYSE trading hours
';6h30m'

ISO week dates

Use ISO 8601 week format for weekly schedules:

-- Week 1 of 2024 (entire week)
SELECT * FROM trades WHERE ts IN '2024-W01';

-- Monday of week 1 (day 1 = Monday)
SELECT * FROM trades WHERE ts IN '2024-W01-1';

-- Friday of week 1 at 09:00
SELECT * FROM trades WHERE ts IN '2024-W01-5T09:00';

Week bracket expansion

-- First 4 weeks of the year
SELECT * FROM trades WHERE ts IN '2024-W[01..04]';

-- Weekdays (Mon-Fri) of week 1
SELECT * FROM trades WHERE ts IN '2024-W01-[1..5]';

-- Every Monday and Friday of weeks 1-4
SELECT * FROM trades WHERE ts IN '2024-W[01..04]-[1,5]';

Day-of-week values

Value	Day
1	Monday
2	Tuesday
3	Wednesday
4	Thursday
5	Friday
6	Saturday
7	Sunday

Complete examples

Trading hours

-- NYSE trading hours for January workdays
SELECT * FROM nyse_trades
WHERE ts IN '2024-01-[01..31]T09:30@America/New_York#workday;6h30m';

-- Compare trading sessions across markets
SELECT * FROM global_trades
WHERE ts IN '2024-01-15T[09:30@America/New_York,08:00@Europe/London,09:00@Asia/Tokyo];6h';

Scheduled reports

-- Weekly Monday standup (52 weeks)
SELECT * FROM standup_notes
WHERE ts IN '2024-W[01..52]-1T09:00;1h';

-- Bi-weekly Friday reports
SELECT * FROM reports
WHERE ts IN '2024-W[02,04,06,08,10,12]-5T14:00;2h';

Rolling windows

-- Last 5 trading days at market open
SELECT * FROM prices
WHERE ts IN '[$today - 5bd..$today - 1bd]T09:30@America/New_York;1m';

-- Same hour comparison across recent days
SELECT * FROM metrics
WHERE ts IN '[$today - 2d, $yesterday, $today]T14:00;1h';

Real-time monitoring

-- Last 2 hours of data
SELECT * FROM sensor_data
WHERE ts IN '$now - 2h..$now';

-- Last 30 minutes
SELECT * FROM metrics
WHERE ts IN '$now - 30m..$now';

-- Last 90 seconds (useful for dashboards)
SELECT * FROM logs
WHERE ts IN '$now - 90s..$now';

-- Sub-second precision for high-frequency data
SELECT * FROM hft_data
WHERE ts IN '$now - 500T..$now';

-- Hourly snapshots from last 4 hours
SELECT * FROM trades
WHERE ts IN '[$now - 4h, $now - 3h, $now - 2h, $now - 1h, $now];5m';

Maintenance windows

-- Weekend maintenance (every Sat/Sun at 02:00)
SELECT * FROM system_logs
WHERE ts IN '2024-01-[01..31]T02:00#weekend;4h';

-- Quarterly maintenance (first Sunday of each quarter)
SELECT * FROM maintenance
WHERE ts IN '2024-[01,04,07,10]-[01..07]T02:00#Sun;6h';

Performance

TICK expressions are fully optimized by QuestDB's query engine:

Interval scan — Each generated interval uses binary search on the designated timestamp
Partition pruning — Partitions outside all intervals are skipped entirely
Parallel expansion — Complex expressions generate multiple efficient interval scans

A TICK expression like '2024-01-[01..31]T09:00#workday;8h' (22 workdays) performs comparably to 22 separate simple queries, but with a single parse.

Use EXPLAIN to see the generated intervals:

EXPLAIN SELECT * FROM trades
WHERE ts IN '2024-01-[15,16,17]T09:00;1h';

Error messages

Error	Cause
`Unclosed '[' in interval`	Missing closing bracket
`Empty bracket expansion`	Nothing inside brackets
`Range must be ascending: 15..10`	End before start in range
`Invalid timezone: xyz`	Unknown timezone
`Unknown date variable: $invalid`	Unrecognized variable
`Invalid day name: xyz`	Unknown day in filter

Grammar summary​

Why TICK​

Quick start​

Syntax order​

Quick reference​

Interval behavior​

Whitespace​

Interval merging​

Optional brackets for date variables​

Date variables​

Date arithmetic​

Date variable ranges​

Mixed date lists​

Bracket expansion​

Multiple brackets (Cartesian product)​

Bracket positions​

Date lists​

Date lists with time suffix​

Time lists​

Timezone support​

Supported timezone formats​

Per-element timezones​

Day-of-week filter​

Available filters​

Filter with timezone​

Duration suffix​

Time units​

Multi-unit durations​

ISO week dates​

Week bracket expansion​

Day-of-week values​

Complete examples​

Trading hours​

Scheduled reports​

Rolling windows​

Real-time monitoring​

Maintenance windows​

Performance​

Error messages​

See also​