Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f52EGSE16741 for ; Sat, 2 Jun 2001 10:16:28 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f52EGCR11608; Sat, 2 Jun 2001 10:16:12 -0400 (EDT) To: Larry Rosenman cc: PostgreSQL SQL List Subject: Re: trying to summarize into a new table by time... In-reply-to: <20010602051258.A18140@lerami.lerctr.org> References: <20010602051258.A18140@lerami.lerctr.org> Comments: In-reply-to Larry Rosenman message dated "Sat, 02 Jun 2001 05:12:58 -0500" Date: Sat, 02 Jun 2001 10:16:12 -0400 Message-ID: <11605.991491372@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200106/14 X-Sequence-Number: 3217 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. regards, tom lane