Received: from lerami.lerctr.org (lerami.lerctr.org [207.158.72.11]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f52EieE27439 for ; Sat, 2 Jun 2001 10:44:40 -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 f52EiZ1j001799; Sat, 2 Jun 2001 09:44:35 -0500 (CDT) (envelope-from ler) Date: Sat, 2 Jun 2001 09:44:35 -0500 From: Larry Rosenman To: Tom Lane Cc: PostgreSQL SQL List Subject: Re: trying to summarize into a new table by time... Message-ID: <20010602094435.A1773@lerami.lerctr.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <11605.991491372@sss.pgh.pa.us> User-Agent: Mutt/1.3.18i X-Mailer: Mutt http://www.mutt.org/ X-Archive-Number: 200106/15 X-Sequence-Number: 3218 * Tom Lane [010602 09:19]: > Larry Rosenman writes: > > where early >= '2001-01-01 00:00:00' and > > early <= '2001-01-02 05:59:59' > > > BUT, I'm wondering if there is an easy way to generate the obvious > > where clauses automatically? > > Assuming this is being fired from a cron job started at or just after > each interval, you could do something with date_trunc: > > regression=# select now(); > now > ------------------------ > 2001-06-02 10:12:37-04 > (1 row) > > regression=# select date_trunc('hour', now()); > date_trunc > ------------------------ > 2001-06-02 10:00:00-04 > (1 row) > > regression=# select date_trunc('hour', now()) - '6 hours'::interval; > ?column? > ------------------------ > 2001-06-02 04:00:00-04 > (1 row) > > Or, if you're running the script once a day at midnight, use > current_date (or equivalently date_trunc('day',...)) and subtract > appropriate intervals from that. Thanks, Tom! You are Terrific! One more question, will the sum() on a bigint column cast to float happen in floating point? I hope so! This data is huge numbers! Thanks again for always answering most of my queries. LER > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- 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