FILL on keyed queries with arbitrary intervals

You want to sample data and fill any potential gaps with interpolated values, using a time interval defined by a starting and ending timestamp, not only between the first and last existing row in the filtered results.

Problem​

QuestDB has a built-in SAMPLE BY .. FROM/TO syntax available for non-keyed queries (queries that include only aggregated columns beyond the timestamp), and for the NULL FILL strategy.

If you use FROM/TO in a keyed query (for example, an OHLC with timestamp, symbol, and aggregations) you will get the following error: FROM-TO intervals are not supported for keyed SAMPLE BY queries.

Solution​

"Sandwich" your data by adding artificial boundary rows at the start and end of your time interval using UNION ALL. These rows contain your target timestamps with nulls for all other columns. Then you can use FILL without the FROM/TO keywords and get results for every sampled interval within those arbitrary dates.

FILL arbitrary interval with keyed SAMPLE BYDemo this query

DECLARE
  @start_ts := dateadd('m', -2, now()),
  @end_ts := dateadd('m', 2, now())
WITH
sandwich AS (
  SELECT * FROM (
    SELECT @start_ts AS timestamp, null AS symbol, null AS open, null AS high, null AS close, null AS low
    UNION ALL
    SELECT timestamp, symbol, open_mid AS open, high_mid AS high, close_mid AS close, low_mid AS low
    FROM core_price_1s
    WHERE timestamp BETWEEN @start_ts AND @end_ts
    UNION ALL
    SELECT @end_ts AS timestamp, null AS symbol, null AS open, null AS high, null AS close, null AS low
  ) ORDER BY timestamp
),
sampled AS (
  SELECT
    timestamp,
    symbol,
    first(open) AS open,
    first(high) AS high,
    first(low) AS low,
    first(close) AS close
  FROM sandwich
  SAMPLE BY 30s
  FILL(PREV, PREV, PREV, PREV, 0)
)
SELECT * FROM sampled WHERE open IS NOT NULL AND symbol IN ('EURUSD', 'GBPUSD');

This query:

  1. Creates boundary rows with null values at the start and end timestamps
  2. Combines them with filtered data using UNION ALL
  3. Applies ORDER BY timestamp to preserve the designated timestamp
  4. Performs SAMPLE BY with FILL - gaps are filled across the full interval
  5. Filters out the artificial boundary rows using open IS NOT NULL

The boundary rows ensure that gaps are filled from the beginning to the end of your specified interval, not just between existing data points.

Related Documentation