Funny, I just yesterday wrote the same exact thing (ip/asn accounting
based on netflow)
The problem is to iterate over range, which SQL doesn't like to do, unless
given an explicit range table. Thus, there are possible solutions.
a) do it like this:
insert into traffic_summary
...
group by date_part('epoch',early)/60/60/2
(assuming 2-hour aggregation interval)
However, using this method, you won't get any data in traffic_summary
when there was no traffic. If you want that, use...
b) have an explicit table traffic_periods(period_start timestamp,
period_end timestamp), prepopulated with data, and then doing
insert ...
select ...
from traffic t,traffic_periods tp
where t.early between period_start and period_end
group by tp.oid
On Sat, 2 Jun 2001, Larry Rosenman wrote:
> insert into traffic_summary
> select asn,protocol,
> cast(sum(pkts_src) as float) as pkts_src,
> cast(sum(pkts_dst) as float) as pkts_dst,
> cast(sum(bytes_src) as float) as bytes_src,
> cast(sum(bytes_dst) as float) as bytes_dst,
> cast(sum(secs_src) as float) as secs_src,
> cast(sum(secs_dst) as float) as secs_dst,
> min(early) as early,
> max(late) as late
> from traffic
> where early >= '2001-01-01 00:00:00' and
> early <= '2001-01-02 05:59:59'
> GROUP BY asn,protocol;
>
> BUT, I'm wondering if there is an easy way to generate the obvious
> where clauses automatically?
>