How to put right interval bound of timestamp in SAMPLE BY query

Community
1

I have a question regarding the SAMPLE BY query method, which live demo has in the OHLC downsample example. I copied the query down below

/* Aggregations for the BTC-USDT for today downsampled in 15-minute intervals.
 We use the SQL extension SAMPLE BY to aggregate data at regular intervals. QuestDB
 ingests live market data from the OKX API. */
SELECT
    timestamp, symbol,
    first(price) AS open,
    last(price) AS close,
    min(price),
    max(price),
    sum(amount) AS volume
FROM trades
WHERE symbol = 'BTC-USDT' AND timestamp IN today()
SAMPLE BY 15m;

As in the demo, the records are grouped in a 15 minute interval. For example, records between 2025-03-22T00:00:00.000000Z and 2025-03-22T00:15:00.000000Z will be aggregated with timestamp of 2025-03-22T00:00:00.000000Z.

However, as this timestamp will cause a future data problem, I want to ask if it’s possible to give this aggregation 2025-03-22T00:15:00.000000Z (which is the right bound of the interval rather than left)?

2

Hi @whoosh ,

Of course, no problem.

You can simply shift the timestamp in your SELECT:

(
SELECT
    dateadd('m', 15, timestamp) timestamp, symbol,
    first(price) AS open,
    last(price) AS close,
    min(price),
    max(price),
    sum(amount) AS volume
FROM trades
WHERE symbol = 'BTC-USDT' AND timestamp IN today()
SAMPLE BY 15m
) ORDER BY timestamp;

I tagged on a subquery with an ORDER BY timestamp, so that it will be seen as a designated (ordered) timestamp. This will help if you nest this query within others.

3

Thanks for the solution, this is exactly what I needed!