Right interval bound with SAMPLE BY

Use the right interval bound (end of interval) instead of the left bound (start of interval) for SAMPLE BY timestamps.

Problem

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 are aggregated with timestamp 2025-03-22T00:00:00.000000Z.

You want the aggregation to show 2025-03-22T00:15:00.000000Z (the right bound of the interval rather than left).

Solution

Simply shift the timestamp in the SELECT:

SELECT
    dateadd('m', 15, timestamp) AS timestamp, symbol,
    first(price) AS open,
    last(price) AS close,
    min(price),
    max(price),
    sum(quantity) AS volume
FROM fx_trades
WHERE symbol = 'EURUSD' AND timestamp IN '$today'
SAMPLE BY 15m;

:::info Related Documentation

• SAMPLE BY
• dateadd() :::