Finding time ranges in multiple rows

Question

I have a table with (suppose) just 2 columns:

- fecha (a date)
- ignicion (0/1 representing boolean state)

The goal is to retrieve which periods of time was ignicion held at '1', for such task I figured out an iterative approach like the one in the following queries:

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=1  and fecha > '2016-05-11 00:00:00-03' and fecha < '2016-05-12 00:00:00-03'; 

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=0  and fecha > '2016-05-11 03:01:38+00' and fecha < '2016-05-12 00:00:00-03';

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=1  and fecha > '2016-05-11 03:42:19+00' and fecha < '2016-05-12 00:00:00-03';

Programming that is no problem, but executing it might be an issue, as the report may involve a month's wide of data, and not from just a single table being "scanned", but around 500 with the same data structure (just different table name).

Any ideas on how this could be achieved in a cleaner/better/faster way?

I've been advised on "window" functions, but I don't really know if any of those actually fit this task.

Answer 1

This can be surprisingly simple with a smart application of a window function.
Based on this table definition:

CREATE TEMP TABLE tbl (
  fecha timestamp PRIMARY KEY
, ignicion bool NOT NULL  -- use an actual boolean!!
);

Query:

SELECT period, min(fecha), max(fecha), count(*) AS row_ct
FROM  (
   SELECT *, count(*) FILTER (WHERE NOT ignicion) OVER (ORDER BY fecha) AS period
   FROM   tbl
   ) sub
WHERE  ignicion
GROUP  BY period
ORDER  BY period;

... combining the new aggregate FILTER clause (pg 9.4+) with a window function in a subquery.

• Return counts for multiple ranges in a single SELECT statement

The trick is to compute a running count rows with ignicion = FALSE in the subquery, and then group rows with ignicion = TRUE by that count.

If fecha is not unique or one of the columns can be NULL, you need to do more.