Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE ticker(t_id serial, price int, t timestamp);

INSERT INTO ticker(price, t)  -- only few sample rows
SELECT (random()* 100)::int
     , generate_series(timestamp '2012-07-18 00:00'
                     , timestamp '2012-07-18 01:05'
                     , interval '7 min');

CREATE TABLE



INSERT 0 10


-- pg 14+
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;


bin
min_prize
max_prize
first_price
last_price


2012-07-18 00:00:00
3
3
3
3

2012-07-18 00:15:00
3
72
72
72

2012-07-18 00:30:00
3
72
70
70

2012-07-18 00:45:00
3
87
32
32

2012-07-18 01:00:00
3
87
72
72



SELECT 5


-- pg 13
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;


hour
quarter
min_prize
max_prize
first_price
last_price


2012-07-18 00:00:00
0
3
3
3
3

2012-07-18 00:00:00
1
3
72
72
72

2012-07-18 00:00:00
2
3
72
70
70

2012-07-18 00:00:00
3
3
87
32
32

2012-07-18 01:00:00
0
3
87
72
72



SELECT 5


-- pg 13 - or for a complete grid
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;


bin
min_prize
max_prize
first_prize
last_prize


2012-07-18 00:00:00
3
3
3
3

2012-07-18 00:05:00
43
43
43
43

2012-07-18 00:10:00
30
30
30
30

2012-07-18 00:15:00
null
null
null
null

2012-07-18 00:20:00
72
72
72
72

2012-07-18 00:25:00
54
54
54
54

2012-07-18 00:30:00
null
null
null
null

2012-07-18 00:35:00
70
70
70
70

2012-07-18 00:40:00
87
87
87
87

2012-07-18 00:45:00
32
32
32
32

2012-07-18 00:50:00
null
null
null
null

2012-07-18 00:55:00
79
79
79
79

2012-07-18 01:00:00
72
72
72
72

2012-07-18 01:05:00
null
null
null
null

2012-07-18 01:10:00
null
null
null
null

2012-07-18 01:15:00
null
null
null
null



SELECT 16

bin	min_prize	max_prize	first_price	last_price
2012-07-18 00:00:00	3	3	3	3
2012-07-18 00:15:00	3	72	72	72
2012-07-18 00:30:00	3	72	70	70
2012-07-18 00:45:00	3	87	32	32
2012-07-18 01:00:00	3	87	72	72

bin	min_prize	max_prize	first_prize	last_prize
2012-07-18 00:00:00	3	3	3	3
2012-07-18 00:05:00	43	43	43	43
2012-07-18 00:10:00	30	30	30	30
2012-07-18 00:15:00	null	null	null	null
2012-07-18 00:20:00	72	72	72	72
2012-07-18 00:25:00	54	54	54	54
2012-07-18 00:30:00	null	null	null	null
2012-07-18 00:35:00	70	70	70	70
2012-07-18 00:40:00	87	87	87	87
2012-07-18 00:45:00	32	32	32	32
2012-07-18 00:50:00	null	null	null	null
2012-07-18 00:55:00	79	79	79	79
2012-07-18 01:00:00	72	72	72	72
2012-07-18 01:05:00	null	null	null	null
2012-07-18 01:10:00	null	null	null	null
2012-07-18 01:15:00	null	null	null	null