MySQL/Postgres query 5 minutes interval data
Recursive CTE
Since each row depends on the one before, it is hard to solve with a set-based approach. Resorting to a recursive CTE (which is standard SQL):
WITH RECURSIVE cte AS ( (SELECT ts FROM tbl ORDER BY ts LIMIT 1) UNION ALL (SELECT t.ts FROM cte c JOIN tbl t ON t.ts >= c.ts + interval '5 min' ORDER BY t.ts LIMIT 1) )SELECT * FROM cte ORDER BY ts;
Note the update from my first draft:
Aggregate functions are not allowed in a recursive CTE. I substituted with ORDER BY
/ LIMIT 1
, which should be fast when supported by an index on ts
.
The parentheses around each leg of the UNION
query are necessary to allow LIMIT
, which would otherwise only be permitted once at the end of a UNION
query.
PL/pgSQL function
A procedural solution (example with a plpgsql function) iterating through the sorted table would probably be a lot faster, since it can make do with a single table scan:
CREATE OR REPLACE FUNCTION f_rowgrid(i interval) RETURNS SETOF timestamp AS$func$DECLARE _this timestamp; _last timestamp := '-infinity'; -- init so that 1 row passesBEGINFOR _this IN SELECT ts FROM tbl ORDER BY 1LOOP IF _this >= _last + i THEN RETURN NEXT _this; _last := _this; END IF;END LOOP;END$func$ LANGUAGE plpgsql;
Call:
SELECT * FROM f_rowgrid('5 min')
SQL Fiddle demonstrating both.
Here is a somewhat more complex example for this type of plpgsql function:
Could easily be made generic with dynamic SQL and EXECUTE
to work for arbitrary tables.