Retrieve aggregates for arbitrary time intervals

generate-series, postgresql, sql

Solution

Postgres 14 or newer:

Postgres 14 added the function `date_bin()`. Now this works for any interval. Example with 15 minutes:

SELECT DISTINCT ON (1)
       date_bin('15 min', t, timestamp '2012-07-18 00:00') AS bin
     , min(price)        OVER w  AS min_prize
     , max(price)        OVER w  AS max_prize
     , price                     AS first_price
     , last_value(price) OVER w  AS last_price
FROM   ticker
WINDOW w AS (PARTITION BY 1 ORDER BY t)
ORDER  BY 1, t;

fiddle

To include empty bins (no matching rows), you still need to `LEFT JOIN` to a grid like below.

Postgres 13 or older

For any number of minutes. Example with 15 minutes:

SELECT DISTINCT ON (1, 2)
       date_trunc('hour', t) AS hour
     , floor(EXTRACT(minute FROM t) / 15) AS quarter
     , min(price) OVER w AS min_prize
     , max(price) OVER w AS max_prize
     , price AS first_price
     , last_value(price) OVER w AS last_price
FROM   ticker
WINDOW w AS (PARTITION BY 1, 2 ORDER BY t)
ORDER  BY 1, 2, t;

A more generic solution for any regular time intervals, across any period of time, and including empty bins (no matching rows):

SELECT DISTINCT ON (grid.bin)
       grid.bin
     , min(price)        OVER w  AS min_prize
     , max(price)        OVER w  AS max_prize
     , price                     AS first_prize
     , last_value(price) OVER w  AS last_prize
FROM   generate_series(timestamp '2012-07-18 00:00'  -- your time range here
                     , timestamp '2012-07-18 01:15'
                     , interval  '5 min') grid(bin)
LEFT   JOIN ticker t ON t.t >= grid.bin                     -- use JOIN to exclude empty intervals
                    AND t.t <  grid.bin + interval '5 min'  -- don't use BETWEEN
WINDOW w AS (PARTITION BY grid.bin ORDER BY t)
ORDER  BY grid.bin, t.t;

Related:

- Best way to count rows by arbitrary time intervals

- Select first row in each GROUP BY group?

Problem

This is the query I have so far, to create daily bars: ``` SELECT DISTINCT date_trunc('hour',t) AS date, min(price) OVER w, max(price) OVER w, first_value(price) OVER w, last_value(price) OVER w FROM ticker WINDOW w AS (PARTITION BY date_trunc('hour',t)); ``` Changing 'hour' to 'min' or 'day' would give me the bars corresponding to these units. However, what if I want 5 min or 15 min bars? `date_trunc()` doesn't support these and I'm looking for a nice elegant way to do it.

Original source

Related problems