SQLize Online / PHPize Online  /  SQLtest Online

create table readings ( id serial primary key not null, value real not null, start_ts timestamp not null, end_ts timestamp ); insert into readings(value, start_ts, end_ts) select random(), t, t + interval '1 minute' from generate_series( now() - interval '5 months', now(), interval '1 minute' ) g(t); create index on readings ( date_trunc('day', start_ts), value ); vacuum analyze readings; explain (analyze, buffers) select day, hi, lo from ( select min( date_trunc('day', start_ts) ), max( date_trunc('day', start_ts) ) from readings ) bounds(old, new) cross join lateral ( select day from generate_series(old, new, interval '1 day') days(day) ) days(day) cross join lateral ( select min(value), max(value) from readings where date_trunc('day', start_ts) = days.day ) data(hi, lo)