CODEWITHSUNDEEP
sqlpostgresqlgenerate-series
sob727
sob727

Reputation: 59

Retrieve aggregates for arbitrary time intervals

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.

Upvotes: 4

Views: 1692

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658937

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:

Upvotes: 7

Related Questions