Received: from lerami.lerctr.org (lerami.lerctr.org [207.158.72.11]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f52AD6E30223 for ; Sat, 2 Jun 2001 06:13:07 -0400 (EDT) (envelope-from ler@lerctr.org) Received: (from ler@localhost) by lerami.lerctr.org (8.12.0.Beta7/8.12.0.Beta7/20010318/$Revision: 1.22 $) id f52ACwCw018212 for pgsql-sql@postgresql.org; Sat, 2 Jun 2001 05:12:58 -0500 (CDT) (envelope-from ler) Date: Sat, 2 Jun 2001 05:12:58 -0500 From: Larry Rosenman To: PostgreSQL SQL List Subject: trying to summarize into a new table by time... Message-ID: <20010602051258.A18140@lerami.lerctr.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.3.18i X-Archive-Number: 200106/12 X-Sequence-Number: 3215 I have a LARGE table with 5 minute summary information: -- -- TOC Entry ID 9 (OID 539300) -- -- Name: traffic Type: TABLE Owner: ler -- CREATE TABLE "traffic" ( "asn" integer, "protocol" integer, "pkts_src" integer, "pkts_dst" integer, "bytes_src" integer, "bytes_dst" integer, "secs_src" integer, "secs_dst" integer, "early" timestamp with time zone, "late" timestamp with time zone ); I'd like to summarize it into: -- -- TOC Entry ID 10 (OID 539319) -- -- Name: traffic_summary Type: TABLE Owner: ler -- CREATE TABLE "traffic_summary" ( "asn" integer, "protocol" integer, "pkts_src" double precision, "pkts_dst" double precision, "bytes_src" double precision, "bytes_dst" double precision, "secs_src" double precision, "secs_dst" double precision, "early" timestamp with time zone, "late" timestamp with time zone ); Where we group into six hour groupings. I came up with the following: 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? -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749