xbar: Count Rows in Time Buckets

Question

Given the following table of market data:

t:([] sym:`A`A`A`A`B`B`B`C`C`C;
      time:2026.06.11D00:01:00 2026.06.11D00:07:00 2026.06.11D00:18:00 2026.06.11D00:31:00
           2026.06.11D00:04:00 2026.06.11D00:16:00 2026.06.11D00:44:00
           2026.06.11D00:02:00 2026.06.11D00:29:00 2026.06.11D00:47:00;
      price:101.2 102.5 103.1 104.0 99.8 100.4 101.0 200.1 201.5 202.0)

Write a qSQL query to return the number of rows per 15-minute interval, per symbol.

Solution

select numRows:count i by sym,timeBucket:15 xbar time.minute from t

Result:

sym timeBucket| numRows
--------------| -------
A   00:00     | 2
A   00:15     | 1
A   00:30     | 1
B   00:00     | 1
B   00:15     | 1
B   00:30     | 1
C   00:00     | 1
C   00:15     | 1
C   00:45     | 1

Alternative Solution

Using timestamp buckets directly:

select numRows:count i by sym,timeBucket:00D00:15 xbar time from t

Result:

sym timeBucket                   | numRows
---------------------------------| -------
A   2026.06.11D00:00:00.000000000| 2
A   2026.06.11D00:15:00.000000000| 1
A   2026.06.11D00:30:00.000000000| 1
B   2026.06.11D00:00:00.000000000| 1
B   2026.06.11D00:15:00.000000000| 1
B   2026.06.11D00:30:00.000000000| 1
C   2026.06.11D00:00:00.000000000| 1
C   2026.06.11D00:15:00.000000000| 1
C   2026.06.11D00:45:00.000000000| 1

Another equivalent timestamp-based form is:

select numRows:count i by sym,timeBucket:(60*15*1000000000) xbar time from t

Explanation

The goal is to group records by:

sym
A 15-minute time bucket

The expression:

time.minute

converts each timestamp to its time of day as a minute value (hours and minutes, hh:mm), dropping the date and seconds.

For example:

`minute$2026.06.11D00:18:00

returns:

00:18

(time.minute is the dot-notation form of the same cast, applied to the column.)

The expression:

15 xbar time.minute

rounds each minute down to the nearest 15-minute boundary.

For example:

15 xbar 00:18

returns:

00:15

and:

15 xbar 00:47

returns:

00:45

So the query:

select numRows:count i by sym,timeBucket:15 xbar time.minute from t

groups the table by symbol and 15-minute bucket, then counts the number of rows in each group.

Timestamp Buckets

If you want the bucket to remain a full timestamp, use:

00D00:15 xbar time

For example:

00D00:15 xbar 2026.06.11D00:18:00.000000000

returns:

2026.06.11D00:15:00.000000000

This is useful when the date needs to be preserved.

The same interval can also be written as a number of nanoseconds, 60*15*1000000000, so this query is equivalent:

select numRows:count i by sym,timeBucket:(60*15*1000000000) xbar time from t

Summary

Use:

15 xbar time.minute

when you only need the time-of-day bucket.

Use:

00D00:15 xbar time

when you want to preserve the full timestamp, including the date.

Both approaches are common when aggregating market data into fixed time intervals.