Chris Chris - 1 year ago 89
SQL Question

postgres select aggregate timespans

I have a table with the following structure:

timstamp-start, timestamp-stop

i am only interested in continuous timespans e.g. the break between a timestamp-end and the following timestamp-start is less than 3.

How could I get the aggregated covered timespans as a result:


The reason I am considering this is because a user may request a timespan that would need to return several thousand rows. However, most records are continous and using above method could potentially reduce many thousand of rows down to just a dozen. Or is the added computation not worth the savings in bandwith and latency?

Answer Source

You can group the time stamps in three steps:

  1. Add a flag to determine where a new period starts (that is, a gap greater than 3).
  2. Cumulatively sum the flag to assign groupings.
  3. Re-aggregate with the new groupings.

The code looks like:

select min(ts_start) as ts_start, max(ts_end) as ts_end
from (select t.*,
             sum(flag) over (order by ts_start) as grouping
      from (select t.*,
                   (coalesce(ts_start - lag(ts_end) over (order by ts_start),0) > 3)::int as flag
            from t
           ) t
     ) t
group by grouping;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download