database - How to put right interval bound of timestamp in SAMPLE BY query - Stack Overflow

admin管理员组

文章数量:1394165

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, 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)?

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, 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)?

• database
• time-series
• questdb

Share Improve this question Follow asked Mar 27 at 13:13 Javier RamirezJavier Ramirez 4,08511 gold badge2727 silver badges3636 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Of course, no problem.

We can simply shift the timestamp in the 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;

Note that on executing this query, QuestDB is not displaying the timestamp in green on the web console. This is because we are not outputting the original designated timestamp, but a derived column. If you are not going to use this query in a subquery, then you are good to go. But supposing you want to use the output of this query in a subquery that requires a designated timestamp, you could do something like this to force sort order by the derived timestamp column, so everything would work.

(
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;

本文标签： databaseHow to put right interval bound of timestamp in SAMPLE BY queryStack Overflow