X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4E4F99DCC57 for ; Fri, 24 Mar 2006 18:09:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68183-02 for ; Fri, 24 Mar 2006 18:09:56 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by postgresql.org (Postfix) with ESMTP id 07EF09DCC24 for ; Fri, 24 Mar 2006 18:09:53 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 44AEB350D9; Fri, 24 Mar 2006 14:09:55 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 41B11350D5; Fri, 24 Mar 2006 14:09:55 -0800 (PST) Date: Fri, 24 Mar 2006 14:09:55 -0800 (PST) From: Stephan Szabo To: Bradley Kieser Cc: PgSQL ADMIN Subject: Re: OT: using column in an interval In-Reply-To: <4422A13D.8060804@sms-speedway.com> Message-ID: <20060324140436.N95370@megazone.bigpanda.com> References: <4422A13D.8060804@sms-speedway.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.14 required=5 tests=[AWL=0.140] X-Spam-Score: 0.14 X-Spam-Level: X-Archive-Number: 200603/248 X-Sequence-Number: 21005 On Thu, 23 Mar 2006, Bradley Kieser wrote: > All, > > I know that this isn't strictly an admin question but please forgive me > for asking. > I am writing a (admin) job which does a select off a PG database based > on three columns: > > last_backup: timestamp > backup_unit: integer - Represents day, week, quarter, annual, etc. The > text is stored in backup_code (e.g. 'days', 'months') > backup_period: integer - Represents the skip factor. > > i.e. if backup_unit is 1 (days) and period is 3, then together they > represent "every three days". > > Obviously I want to do something like: > > select id, client > from backupSchedule > where last_backup + interval backup_period backup_code <= CURRENT_DATE > > > However, interval seems to only take text such as > inverval '3 days' > > and I get an error even with this: > > select id, client > from backupSchedule > where last_backup + interval backup_period::text || backup_code <= > CURRENT_DATE > > Can someone please point me to the right statement to use for > column-based interval arithmetic? > The docs all give hardcoded text strings in examples. Not found one yet > with a proper column-based query! Well, that's because the interval syntax is for interval literals. CAST( backup_period || ' ' || backup_code AS interval) should give you an interval. If the units were constant, I'd say that using integer * interval is a better idea, but I think you'd need a function that say took backup_unit and gave back an interval of 1 to make that work, but that would possibly be cleaner overall.