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 3825A9DC8A5 for ; Fri, 24 Mar 2006 19:42:03 -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 79564-07 for ; Fri, 24 Mar 2006 19:42:05 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 185E79DC829 for ; Fri, 24 Mar 2006 19:42:00 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k2ONfw0g012668; Fri, 24 Mar 2006 18:42:01 -0500 (EST) To: Stephan Szabo cc: Bradley Kieser , PgSQL ADMIN Subject: Re: OT: using column in an interval In-reply-to: <20060324140436.N95370@megazone.bigpanda.com> References: <4422A13D.8060804@sms-speedway.com> <20060324140436.N95370@megazone.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Fri, 24 Mar 2006 14:09:55 -0800" Date: Fri, 24 Mar 2006 18:41:58 -0500 Message-ID: <12667.1143243718@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.114 required=5 tests=[AWL=0.114] X-Spam-Score: 0.114 X-Spam-Level: X-Archive-Number: 200603/250 X-Sequence-Number: 21007 Stephan Szabo writes: > On Thu, 23 Mar 2006, Bradley Kieser wrote: >> 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. > 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. This really seems like a case of a poorly chosen representation. Why not just have the backup interval as an interval column, ie last_backup: timestamptz (not timestamp, btw) backup_interval: interval, can be eg '3 days' or '1 month' or whatever. Then you can do WHERE last_backup + backup_interval <= current_timestamp regards, tom lane