Group by 15 minutes interval

group-by-15-minutes.sql

with
avg_by_hour as (
  select
    id,
    date_trunc('hour', time) + (round(extract('minute' from time) / 15) * 15) * interval '1 minute' as interval,
    trunc(avg(free)) as free
  from velobike_2016_09_05
  group by id, interval
  order by id, interval
)
select
  id,
  interval,
  free
from avg_by_hour

WITH avg_by_15m AS (
  SELECT load.station_id AS id,
    date_trunc('hour'::text, load.ts) + round(date_part('minute'::text, load.ts) / 15::double precision) * 15::double precision * '00:01:00'::interval AS "interval",
    trunc(avg(load.not_free)) AS not_free
  FROM loadings.availability2017 load
  WHERE load.ts >= date_trunc('day', now())
  GROUP BY load.station_id, (date_trunc('hour'::text, load.ts) + round(date_part('minute'::text, load.ts) / 15::double precision) * 15::double precision * '00:01:00'::interval)
  ORDER BY load.station_id, (date_trunc('hour'::text, load.ts) + round(date_part('minute'::text, load.ts) / 15::double precision) * 15::double precision * '00:01:00'::interval)
)
SELECT avg_by_15m.id,
  avg_by_15m."interval",
  avg_by_15m.not_free
FROM avg_by_15m
ORDER BY avg_by_15m.id, avg_by_15m."interval";

create index idx_loadings on loadings_avg_15m (interval);