SAMPLE BY

Put rows into time buckets (1 minute, 15 minutes, 1 hour, 1 day, 1 week, 1 month) and make candles.

• Category: Time-series
• Keywords: SAMPLE, candles, candle, OHLC, time bucket, bucket, resample, downsample, interval, 1m, 5m, 15m, 1h, 1d, 1w, 1M, 1y, ms, minute, hour, day, week, month, year, FILL, NONE, FILL per column, FROM, TO, FROM TO, PREV, LINEAR, empty buckets, gaps, ALIGN, CALENDAR, FIRST OBSERVATION, OBSERVATION, TIME ZONE, ZONE, Asia/Kathmandu, Nepal time, WITH OFFSET, WITH, WEEK START, WEEK, START, SUNDAY, MONDAY, timestamp_floor, QuestDB
• See also: Candles cookbook, LATEST ON, Time and time zones, GROUP BY and HAVING (implicit-group-by), Aggregate functions, Technical indicators

SAMPLE BY cuts time into equal pieces called buckets (for example 15 minutes) and makes one result row for each bucket. With first, max, min, last and sum this makes candles (open, high, low, close, volume).

• It works on time-series tables (tables with TIMESERIES(column)), and on a view or a subquery whose result has a TIMESTAMP column.
• The time column in the result is the start of each bucket, shown in UTC.
• A bucket with no rows gives no result row. Use FILL to get those rows.

Example: 15-minute CITY candles made from 1-minute candles (sample data: sample)

SELECT time,
       first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close,
       sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
SAMPLE BY 15m
LIMIT 4;

Result:

(4 rows)

SAMPLE BY

Syntax:

SELECT time_column, [key_column, ...] aggregate, ...
FROM table | view | (SELECT ...) alias
[WHERE condition]
SAMPLE BY interval
    [FROM start] [TO end]
    [FILL(NONE | NULL | PREV | LINEAR | constant [, ...])]
    [ALIGN TO CALENDAR | ALIGN TO FIRST OBSERVATION]
    [TIME ZONE 'zone']
    [WITH OFFSET '+HH:MM']
    [WEEK START SUNDAY | MONDAY]
[GROUP BY expression, ...]
[HAVING condition]
[ORDER BY ...] [LIMIT n [OFFSET m]]

Makes one row for each bucket of time (and for each key, like symbol). The options after the interval can come in any order, but each option only once.

• Returns: one row per bucket and key. Without ORDER BY the rows come sorted by bucket start; rows of the same bucket come in the order their key was first seen. Add ORDER BY symbol, time when you want each symbol together.
• NULL: rows whose time is NULL are skipped. Aggregates skip NULL values, as always.
• Notes:
  • first(x) and last(x) use the time order of the rows: the open of the earliest row, the close of the latest row.
  • WHERE runs before SAMPLE BY. Always filter by time (time IN '2026-09-10') so KDB reads only the partitions it needs.
  • A plain column without an aggregate is a key, even when you did not mean it: SELECT time, close ... SAMPLE BY 1d gives one row per different close value.
  • A window function can sit next to SAMPLE BY: it runs after the candles are made, over the candles, like lag(last(close)) OVER (PARTITION BY symbol ORDER BY time). With more than one symbol, always write OVER (PARTITION BY symbol ORDER BY time): without it the series is all candles in time order, so the symbols are mixed. Making the candles in a subquery and the indicator outside works too (see the cookbook).
  • You may also write GROUP BY symbol after the SAMPLE BY options. It gives the same rows.
  • Differences from QuestDB: WEEK START SUNDAY exists; FROM / TO also work with keys (like symbol) and with months; TIME ZONE takes zones with summer time too (summer time); times are milliseconds, not microseconds.

Example: Daily candles for every symbol, made from minute candles (sample data: sample)

SELECT time, symbol,
       first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close,
       sum(volume) AS volume, count(*) AS minutes
FROM ochl_minutes
SAMPLE BY 1d
ORDER BY symbol, time;

Result:

(4 rows)

Example: A window over the candles: yesterday's close next to today's (sample data: sample)

SELECT time, symbol, last(close) AS close,
       lag(last(close)) OVER (PARTITION BY symbol ORDER BY time) AS prev_close
FROM ochl_day
WHERE time IN '2026-09' AND time < '2026-09-04'
SAMPLE BY 1d
ORDER BY symbol, time;

Result:

(9 rows)

Example: Trade log: shares and turnover per day per symbol (sample data: sample)

SELECT date, symbol, sum(quantity) AS shares, round(sum(amount), 2) AS turnover, count(*) AS trades
FROM trade_log
WHERE symbol IN ('CITY', 'COAST')
SAMPLE BY 1d
ORDER BY symbol, date;

Result:

(4 rows)

Example: Weekly volume, only the big weeks (HAVING) (sample data: sample)

SELECT time, symbol, sum(volume) AS volume
FROM ochl_day
WHERE time IN '2026'
SAMPLE BY 1w WEEK START SUNDAY
HAVING sum(volume) > 600000;

Result:

(2 rows)

Example: A plain column without an aggregate becomes a key (sample data: sample)

SELECT time, close
FROM ochl_day
WHERE symbol = 'CITY' AND time IN '2026-09'
SAMPLE BY 1M;

Result:

(8 rows)

Example: SAMPLE BY on a relational table (sample data: sample; shows an error)

SELECT id, count(*) FROM brokers SAMPLE BY 1d;

Result:

Error: SAMPLE BY needs a table with a time column (a TIMESERIES table)

Intervals

Syntax:

SAMPLE BY n ms | n s | n m | n h | n d | n w | n M | n y

The interval is a whole number and a unit, written together without a space: 15m, 4h, 1M.

• Notes:
  • 1m is one minute and 1M is one month. This is the most common mistake.
  • The other units do not care about case: 1H is the same as 1h.
  • Months and years follow the calendar: February is shorter than March.
  • The number must be 1 or more: 0d is an error.

Example: Monthly candles of VOLT (sample data: sample)

SELECT time, symbol, first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close
FROM ochl_day
WHERE symbol = 'VOLT'
SAMPLE BY 1M;

Result:

(3 rows)

Example: 1m is a minute: 5 one-minute rows of COAST (sample data: sample)

SELECT time, last(close) AS close, sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'COAST' AND time IN '2026-09-10'
SAMPLE BY 1m
LIMIT 5;

Result:

(5 rows)

Example: Yearly rows, and a 2-week bucket (sample data: sample)

SELECT time, last(close) AS close, count(*) AS days FROM ochl_day WHERE symbol = 'COAST' SAMPLE BY 1y;
SELECT time, first(open) AS open, last(close) AS close FROM ochl_day WHERE symbol = 'COAST' AND time IN '2026' SAMPLE BY 2w WEEK START SUNDAY;

Result:

SELECT time, last(close) AS close, count(*) AS days FROM ochl_day WHERE symbol = 'COAST' SAMPLE BY 1

(2 rows)

SELECT time, first(open) AS open, last(close) AS close FROM ochl_day WHERE symbol = 'COAST' AND time

(2 rows)

Example: An unknown unit (sample data: sample; shows an error)

SELECT time, count(*) FROM ochl_day SAMPLE BY 1x;

Result:

Error: line 1, column 47: expected a time interval such as 1m, 1h, 1d or 1M (units: ms, s, m, h, d, w, M for month, y for year) but found '1x'

FILL

Syntax:

SAMPLE BY interval FILL(NONE)
SAMPLE BY interval FILL(NULL)
SAMPLE BY interval FILL(PREV)
SAMPLE BY interval FILL(LINEAR)
SAMPLE BY interval FILL(constant)
SAMPLE BY interval FILL(rule, rule, ...)      -- one rule per aggregate column

Says what to do with empty buckets (buckets with no rows), for example minutes when a share did not trade.

• Returns: the same columns, with extra rows for the empty buckets. Key columns (like symbol) are copied into the new rows.
• NULL: FILL(PREV) gives NULL when there is no bucket before (for that key). FILL(LINEAR) gives NULL when a bucket before or after is missing.
• Notes:
  • FILL(rule) with one rule is for all aggregate columns: FILL(PREV) also copies the volume, and FILL(0) also sets the close to 0.
  • FILL(rule, rule, ...) gives each aggregate column its own rule, in the order of the SELECT list. Candles want FILL(PREV, PREV, PREV, PREV, 0): open, high, low and close from the minute before, volume 0. Time and key columns (like symbol) do not count. A column that is an expression of aggregates (last(close) * 2) is one column.
  • With more than one rule, give exactly one per aggregate column. Too many or too few is an error that lists the columns. NONE cannot be mixed with other rules.
  • FILL only fills between the first and the last bucket that have data in the whole result. With many symbols, every symbol gets rows for that whole range. Use FROM / TO to fill a fixed range instead.
  • FILL fills every bucket, also nights and holidays. SAMPLE BY 1m FILL(PREV) over two days makes about 1,200 extra minutes for the night. Filter one trading day with WHERE time IN '...'.
  • The constant is not checked against the column type: FILL('n/a') puts text into a number column.
  • FILL can make at most 10 million rows.
  • The value in FILL must be a constant: FILL(close) is an error.

Example: COAST had no trades from 12:31 to 12:34 Nepal time: no FILL, then FILL(PREV) (sample data: sample)

SELECT time, last(close) AS close, sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'COAST'
  AND time >= '2026-09-10T12:30:00+05:45' AND time < '2026-09-10T12:36:00+05:45'
SAMPLE BY 1m;

SELECT time, last(close) AS close, sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'COAST'
  AND time >= '2026-09-10T12:30:00+05:45' AND time < '2026-09-10T12:36:00+05:45'
SAMPLE BY 1m FILL(PREV);

Result:

SELECT time, last(close) AS close, sum(volume) AS volume FROM ochl_minutes WHERE symbol = 'COAST' AN

(2 rows)

SELECT time, last(close) AS close, sum(volume) AS volume FROM ochl_minutes WHERE symbol = 'COAST' AN

(6 rows)

Example: FILL(NULL), FILL(LINEAR) and FILL(0) on the same gap (sample data: sample)

SELECT time, last(close) AS close, sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'COAST' AND time >= '2026-09-10T12:30:00+05:45' AND time < '2026-09-10T12:36:00+05:45'
SAMPLE BY 1m FILL(NULL);

SELECT time, last(close) AS close, sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'COAST' AND time >= '2026-09-10T12:30:00+05:45' AND time < '2026-09-10T12:36:00+05:45'
SAMPLE BY 1m FILL(LINEAR);

SELECT time, last(close) AS close, count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'COAST' AND time >= '2026-09-10T12:30:00+05:45' AND time < '2026-09-10T12:36:00+05:45'
SAMPLE BY 1m FILL(0);

Result:

SELECT time, last(close) AS close, sum(volume) AS volume FROM ochl_minutes WHERE symbol = 'COAST' AN

(6 rows)

SELECT time, last(close) AS close, sum(volume) AS volume FROM ochl_minutes WHERE symbol = 'COAST' AN

(6 rows)

SELECT time, last(close) AS close, count(*) AS minutes FROM ochl_minutes WHERE symbol = 'COAST' AND

(6 rows)

Example: A rule per column: close from the minute before, volume 0 (sample data: sample)

SELECT time, last(close) AS close, sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'COAST' AND time >= '2026-09-10T12:30:00+05:45' AND time < '2026-09-10T12:36:00+05:45'
SAMPLE BY 1m FILL(PREV, 0);

Result:

(6 rows)

Example: One rule per aggregate column, or one for all (sample data: sample; shows an error)

SELECT time, last(close) AS close, sum(volume) AS volume
FROM ochl_minutes WHERE symbol = 'COAST' SAMPLE BY 1m FILL(PREV, 0, 0);

Result:

Error: FILL has 3 values but the query has 2 aggregate columns (close, volume); expected one FILL value for all of them, or one per aggregate column in SELECT order

Example: Two symbols: each symbol gets every minute of the whole range (sample data: sample)

SELECT time, symbol, last(close) AS close
FROM ochl_minutes
WHERE time >= '2026-09-10T12:30:00+05:45' AND time < '2026-09-10T12:33:00+05:45'
  AND (symbol = 'COAST' OR time >= '2026-09-10T12:32:00+05:45')
SAMPLE BY 1m FILL(PREV)
ORDER BY symbol, time;

Result:

(6 rows)

Example: FILL needs a constant, not a column (sample data: sample; shows an error)

SELECT time, last(close) AS close FROM ochl_minutes WHERE symbol = 'COAST' SAMPLE BY 1m FILL(close);

Result:

Error: line 1, column 94: expected NONE, NULL, PREV, LINEAR or a constant such as 0 in FILL(...) but found 'close'

FROM / TO

Syntax:

SAMPLE BY interval FROM start TO end [FILL(...)]
SAMPLE BY interval FROM start [FILL(...)]
SAMPLE BY interval TO end [FILL(...)]

Sets the range of the buckets (QuestDB). The result runs from the bucket that holds start to the bucket before end, whatever rows there are.

• Returns: with FILL, one row for every bucket of the range (and every key), also before the first row and after the last one. Without FILL, only the buckets that have rows: FROM and TO then work like WHERE time >= start AND time < end.
• NULL: a NULL start or end is an error. FILL(PREV) gives NULL in the buckets before a key's first row.
• Notes:
  • The buckets keep their usual start (ALIGN TO CALENDAR, TIME ZONE, WITH OFFSET). A start inside a bucket begins with that bucket, which then only has the rows from start on.
  • With no rows in the range and no key columns, every bucket is still a filled row (FILL(0) gives zeros). With key columns there is no key to fill, so there are no rows.
  • FROM and TO are constants: a column or a subquery is an error. Not with ALIGN TO FIRST OBSERVATION.
  • The time conditions come from FROM / TO like a written WHERE, so KDB reads only the partitions in the range.

Example: Every minute from 12:28 to 12:35 Nepal time (TO is not included) (sample data: sample)

SELECT time, last(close) AS close, sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'COAST'
SAMPLE BY 1m FROM '2026-09-10T12:28:00+05:45' TO '2026-09-10T12:36:00+05:45' FILL(PREV, 0);

Result:

(8 rows)

Example: FROM / TO without FILL only cut the rows (sample data: sample)

SELECT time, count(*) AS rows
FROM ochl_minutes
WHERE symbol = 'COAST'
SAMPLE BY 1h FROM '2026-09-10T11:00:00+05:45' TO '2026-09-10T13:00:00+05:45'
ALIGN TO CALENDAR TIME ZONE 'Asia/Kathmandu';

Result:

(2 rows)

Example: FROM must be a time (sample data: sample; shows an error)

SELECT time, sum(volume) FROM ochl_minutes SAMPLE BY 1m FROM 'soon';

Result:

Error: line 1, column 62: SAMPLE BY FROM 'soon' is not a time; expected a time such as '2026-09-24T05:00:00Z'

ALIGN TO CALENDAR

Syntax:

SAMPLE BY interval ALIGN TO CALENDAR

Buckets start at round clock times: a 10m bucket starts at :00, :10, :20, ...; a 1d bucket at 00:00. This is the default, so you never need to write it. QuestDB queries that have it run unchanged.

• Notes: the clock is UTC. Use TIME ZONE or WITH OFFSET to move it.

Example: 10-minute buckets on the clock, the first row is at 05:17 (sample data: sample)

SELECT time, first(open) AS open, count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'CITY' AND time >= '2026-09-10T05:17:00Z' AND time < '2026-09-10T05:40:00Z'
SAMPLE BY 10m ALIGN TO CALENDAR;

Result:

(3 rows)

ALIGN TO FIRST OBSERVATION

Syntax:

SAMPLE BY interval ALIGN TO FIRST OBSERVATION

The first bucket starts at the time of the first row (after WHERE). The next buckets follow every interval from there.

• Notes:
  • Not with months or years (1M, 1y): their length changes.
  • Not together with TIME ZONE or WITH OFFSET: those only move calendar buckets.
  • The start depends on the data. When a new earlier row comes in, all buckets move. For charts, calendar buckets are usually better.

Example: The same rows: buckets start at the first row, 05:17 (sample data: sample)

SELECT time, first(open) AS open, count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'CITY' AND time >= '2026-09-10T05:17:00Z' AND time < '2026-09-10T05:40:00Z'
SAMPLE BY 10m ALIGN TO FIRST OBSERVATION;

Result:

(3 rows)

Example: Not with months (sample data: sample; shows an error)

SELECT time, count(*) FROM ochl_day SAMPLE BY 1M ALIGN TO FIRST OBSERVATION;

Result:

Error: ALIGN TO FIRST OBSERVATION needs a fixed interval, not months or years

Example: Not with TIME ZONE (sample data: sample; shows an error)

SELECT time, count(*) FROM ochl_minutes SAMPLE BY 1h ALIGN TO FIRST OBSERVATION TIME ZONE 'Asia/Kathmandu';

Result:

Error: line 1, column 81: TIME ZONE only applies to calendar buckets; expected ALIGN TO CALENDAR (or no ALIGN TO) instead of ALIGN TO FIRST OBSERVATION

TIME ZONE

Syntax:

SAMPLE BY interval TIME ZONE 'zone'
SAMPLE BY interval TIME ZONE '+HH:MM' | '-HH:MM'

Makes the buckets follow the clock of that time zone: hours start at the full hour of that clock, days at its midnight, months at its 1st day.

• Returns: the same columns. The bucket times are still shown in UTC: the Nepal hour 11:00 is shown as 05:15:00.000Z.
• Notes:
  • A zone with summer time ('Europe/London', 'America/New_York') works too: a day bucket starts at local midnight of that day, so it is 23 or 25 hours long when the clocks change (summer time). Nepal has no summer time, so 'Asia/Kathmandu' and '+05:45' are the same.
  • For 1m, 5m and 15m buckets, UTC and Nepal buckets are the same (Nepal is 5 hours 45 minutes ahead). For 1h and longer they are not.
  • Daily rows: first check at what time the table stores a day. Weeks, months and days of daily rows need TIME ZONE only when the rows are at local midnight. See the table below.
  • SAMPLE BY without TIME ZONE cuts on UTC, also after SET TIME ZONE (only functions like date_trunc() and hour() follow the session's zone). Without a zone, ROUND and time IN 'YYYY-MM-DD' work in UTC. Each has a zone form for local days: time IN '2026-09-10' TIME ZONE 'Asia/Kathmandu', date_trunc('day', time, 'Asia/Kathmandu'), timestamp_floor('1d', time, 'Asia/Kathmandu') and a column TIMESTAMP ROUND 1d TIME ZONE 'Asia/Kathmandu'. For the Nepal hour use hour(to_timezone(time, 'Asia/Kathmandu')).

Warning, local-midnight rows: in UTC these rows belong to the day before. Everything that cuts days needs the zone, and nothing gives an error when it is left out:

The week mistake is the easiest to miss, because the result looks normal.

Example: 1-hour candles: UTC hours, then Nepal hours (sample data: sample)

SELECT time, first(open) AS open, last(close) AS close, count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
SAMPLE BY 1h;

SELECT time, first(open) AS open, last(close) AS close, count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
SAMPLE BY 1h TIME ZONE 'Asia/Kathmandu';

Result:

SELECT time, first(open) AS open, last(close) AS close, count(*) AS minutes FROM ochl_minutes WHERE

(5 rows)

SELECT time, first(open) AS open, last(close) AS close, count(*) AS minutes FROM ochl_minutes WHERE

(4 rows)

Example: Nepal days: the bucket starts at Nepal midnight (18:15 UTC) (sample data: sample)

SELECT time, symbol, first(open) AS open, last(close) AS close, count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'CITY'
SAMPLE BY 1d TIME ZONE '+05:45';

Result:

(2 rows)

Example: India time for the same rows (UTC+05:30) (sample data: sample)

SELECT time, count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
SAMPLE BY 1h TIME ZONE 'Asia/Kolkata';

Result:

(5 rows)

Example: The same five trading days, stored at UTC midnight and at Nepal midnight (sample data: sample)

Setup:

CREATE TABLE bars_utc (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time) PARTITION BY MONTH;
CREATE TABLE bars_local (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time) PARTITION BY MONTH;
INSERT INTO bars_utc VALUES ('2026-09-06T00:00:00Z', 'ACME', 1), ('2026-09-07T00:00:00Z', 'ACME', 2),
  ('2026-09-10T00:00:00Z', 'ACME', 3), ('2026-09-13T00:00:00Z', 'ACME', 4), ('2026-09-14T00:00:00Z', 'ACME', 5);
INSERT INTO bars_local VALUES ('2026-09-06T00:00:00+05:45', 'ACME', 1), ('2026-09-07T00:00:00+05:45', 'ACME', 2),
  ('2026-09-10T00:00:00+05:45', 'ACME', 3), ('2026-09-13T00:00:00+05:45', 'ACME', 4), ('2026-09-14T00:00:00+05:45', 'ACME', 5);

-- UTC midnight rows: no TIME ZONE. Weeks start Sunday 6 and Sunday 13 (00:00 UTC).
SELECT time, count(*) AS days, sum(close) AS total FROM bars_utc SAMPLE BY 1w WEEK START SUNDAY;

-- Nepal midnight rows WITHOUT TIME ZONE: wrong. Sunday bars fall into the week before.
SELECT time, count(*) AS days, sum(close) AS total FROM bars_local SAMPLE BY 1w WEEK START SUNDAY;

-- Nepal midnight rows WITH TIME ZONE: right. 18:15 UTC on Saturday is Sunday 00:00 in Nepal.
SELECT time, count(*) AS days, sum(close) AS total FROM bars_local SAMPLE BY 1w WEEK START SUNDAY TIME ZONE 'Asia/Kathmandu';

Result:

SELECT time, count(*) AS days, sum(close) AS total FROM bars_utc SAMPLE BY 1w WEEK START SUNDAY

(2 rows)

SELECT time, count(*) AS days, sum(close) AS total FROM bars_local SAMPLE BY 1w WEEK START SUNDAY

(3 rows)

SELECT time, count(*) AS days, sum(close) AS total FROM bars_local SAMPLE BY 1w TIME ZONE 'Asia/Kath

(2 rows)

Example: Nepal midnight rows: find one Nepal day and show the Nepal date (sample data: sample)

Setup:

CREATE TABLE bars_local (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time) PARTITION BY MONTH;
INSERT INTO bars_local VALUES ('2026-09-10T00:00:00+05:45', 'CITY', 512.4), ('2026-09-13T00:00:00+05:45', 'CITY', 507.6);

-- The UTC date: finds nothing.
SELECT time, close FROM bars_local WHERE time IN '2026-09-10';

-- The Nepal date: finds the bar.
SELECT time, close,
       to_char(time, 'YYYY-MM-DD', 'Asia/Kathmandu') AS nepal_date,
       date_trunc('day', time) AS utc_midnight,
       date_trunc('day', time, 'Asia/Kathmandu') AS nepal_midnight
FROM bars_local WHERE time IN '2026-09-10' TIME ZONE 'Asia/Kathmandu';

Result:

SELECT time, close FROM bars_local WHERE time IN '2026-09-10'

(0 rows)

SELECT time, close, to_char(time, 'YYYY-MM-DD', 'Asia/Kathmandu') AS nepal_date, date_trunc('day', t

(1 row)

Example: ROUND 1d cuts to the UTC day; ROUND 1d TIME ZONE keeps the Nepal date (sample data: sample)

Setup:

CREATE TABLE bars_round (time TIMESTAMP ROUND 1d, symbol SYMBOL, close DOUBLE) TIMESERIES(time) PARTITION BY YEAR;
CREATE TABLE bars_nepal (time TIMESTAMP ROUND 1d TIME ZONE 'Asia/Kathmandu', symbol SYMBOL, close DOUBLE)
  TIMESERIES(time) PARTITION BY YEAR DEDUP KEY (symbol, time);

-- 9 Sep 00:00 UTC: the Nepal date 10 Sep is lost.
INSERT INTO bars_round VALUES ('2026-09-10T00:00:00+05:45', 'CITY', 512.4);
SELECT time, close FROM bars_round;

-- Nepal midnight (18:15 UTC the day before). 15:00 the same Nepal day is the same key, so it replaces the bar.
INSERT INTO bars_nepal VALUES ('2026-09-10T00:00:00+05:45', 'CITY', 512.4);
INSERT OR REPLACE INTO bars_nepal VALUES ('2026-09-10T15:00:00+05:45', 'CITY', 514.0);
SELECT time, close, to_char(time, 'YYYY-MM-DD', 'Asia/Kathmandu') AS nepal_date FROM bars_nepal;

Result:

INSERT INTO bars_round VALUES ('2026-09-10T00:00:00+05:45', 'CITY', 512.4)

1 row(s) inserted

SELECT time, close FROM bars_round

(1 row)

INSERT INTO bars_nepal VALUES ('2026-09-10T00:00:00+05:45', 'CITY', 512.4)

1 row(s) inserted

INSERT OR REPLACE INTO bars_nepal VALUES ('2026-09-10T15:00:00+05:45', 'CITY', 514.0)

0 row(s) inserted, 1 replaced

SELECT time, close, to_char(time, 'YYYY-MM-DD', 'Asia/Kathmandu') AS nepal_date FROM bars_nepal

(1 row)

Example: ROUND TIME ZONE takes fixed offsets only, for now (sample data: sample; shows an error)

CREATE TABLE bars_london (time TIMESTAMP ROUND 1d TIME ZONE 'Europe/London', close DOUBLE) TIMESERIES(time);

Result:

Error: column 'time' of table 'bars_london' has ROUND ... TIME ZONE 'Europe/London', which is not supported (it may change for daylight saving); use a fixed offset like '+05:45'

Example: Hour buckets on the London clock (summer time: +01:00 in September) (sample data: sample)

SELECT time, count(*) AS minutes FROM ochl_minutes WHERE symbol = 'COAST' AND time IN '2026-09-10'
SAMPLE BY 1h TIME ZONE 'Europe/London' LIMIT 3;

Result:

(3 rows)

WITH OFFSET

Syntax:

SAMPLE BY interval WITH OFFSET '+HH:MM'
SAMPLE BY interval WITH OFFSET '-HH:MM'

Moves the start of every bucket by a fixed time. SAMPLE BY 1h WITH OFFSET '+00:15' makes hours that start at :15 (UTC), which are the full hours of the Nepal clock.

• Notes:
  • An offset is not a time zone. SAMPLE BY 1d WITH OFFSET '+05:45' makes days that start at 05:45 UTC, not at Nepal midnight. For Nepal days use TIME ZONE 'Asia/Kathmandu'.
  • For hours, '+00:15' and '-00:45' give the same buckets.
  • With TIME ZONE, the offset moves the buckets on that zone's clock.
  • Not with ALIGN TO FIRST OBSERVATION.
  • QuestDB's SAMPLE BY 1h ALIGN TO CALENDAR WITH OFFSET '00:15' runs unchanged and gives the same buckets.

Example: Nepal-clock hours with an offset, written two ways (sample data: sample)

SELECT time, first(open) AS open, count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
SAMPLE BY 1h ALIGN TO CALENDAR WITH OFFSET '00:15';

SELECT time, count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
SAMPLE BY 1h WITH OFFSET '-00:45';

Result:

SELECT time, first(open) AS open, count(*) AS minutes FROM ochl_minutes WHERE symbol = 'CITY' AND ti

(4 rows)

SELECT time, count(*) AS minutes FROM ochl_minutes WHERE symbol = 'CITY' AND time IN '2026-09-10' SA

(4 rows)

Example: Careful: an offset on days is not Nepal midnight (sample data: sample)

SELECT time, count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'CITY'
SAMPLE BY 1d WITH OFFSET '+05:45';

Result:

(3 rows)

Example: TIME ZONE and WITH OFFSET together: Nepal hours that start at half past (10:30, 11:30, ...) (sample data: sample)

SELECT time, first(open) AS open, last(close) AS close, count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
SAMPLE BY 1h TIME ZONE 'Asia/Kathmandu' WITH OFFSET '+00:30';

Result:

(5 rows)

Example: The hour needs two digits (sample data: sample; shows an error)

SELECT time, count(*) FROM ochl_minutes SAMPLE BY 1h WITH OFFSET '5:45';

Result:

Error: line 1, column 66: expected an offset in single quotes such as '+00:15' or '-05:45' after WITH OFFSET but found '5:45'

WEEK START

Syntax:

SAMPLE BY n w WEEK START SUNDAY
SAMPLE BY n w WEEK START MONDAY

Chooses the first day of a week bucket. The default is MONDAY. On a Sunday-to-Thursday trading week (as on some exchanges), weekly candles need WEEK START SUNDAY.

• Returns: week buckets that start at 00:00 UTC of that day (or of the TIME ZONE clock).
• Notes:
  • Only with a week interval (1w, 2w, ...).
  • QuestDB cannot do this in SQL; the 006 app folds days into Sunday weeks in Go. In KDB one query does it.
  • ochl_day rows are at 00:00 UTC, so these examples have no TIME ZONE. When your daily rows are at local midnight (Nepal 00:00 = 18:15 UTC the day before), add TIME ZONE 'Asia/Kathmandu', or the weeks are stamped on the wrong day (see TIME ZONE).

Example: Sunday-to-Thursday trading weeks of CITY (sample data: sample)

SELECT time, symbol,
       first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close,
       sum(volume) AS volume, count(*) AS days
FROM ochl_day
WHERE symbol = 'CITY' AND time IN '2026'
SAMPLE BY 1w WEEK START SUNDAY;

Result:

(3 rows)

With the default Monday weeks, Sunday 23 August goes into the week of Monday 17 August, alone:

Example: The default week starts on Monday (sample data: sample)

SELECT time, first(open) AS open, last(close) AS close, count(*) AS days
FROM ochl_day
WHERE symbol = 'CITY' AND time IN '2026'
SAMPLE BY 1w;

Result:

(4 rows)

Example: WEEK START with an hour interval (sample data: sample; shows an error)

SELECT time, count(*) FROM ochl_minutes SAMPLE BY 1h WEEK START SUNDAY;

Result:

Error: line 1, column 54: WEEK START needs a week interval; expected SAMPLE BY 1w (or 2w, ...) but the interval is 1h