Received: from mail.interchange.ca (ns.interchange.ca [216.126.79.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DJc9E63227 for ; Wed, 13 Feb 2002 14:38:12 -0500 (EST) (envelope-from michael@fastmail.ca) Received: by mail.interchange.ca (Fastmailer, from userid 555) id 407F02A9D; Wed, 13 Feb 2002 14:37:48 -0500 (EST) MIME-Version: 1.0 Message-Id: <3C6AC08C.0000A1.04458@ns.interchange.ca> To: jason.earl@simplot.com Subject: Re: Time Intervals Cc: pgsql-sql@postgresql.org From: "Michael Richards" X-Fastmail-IP: [24.43.130.241] Received: from 24.43.130.241 by fastmail.ca with HTTP; Wed, 13 Feb 2002 19:37:48 +0000 (UTC) Date: Wed, 13 Feb 2002 14:37:48 -0500 (EST) Content-Transfer-Encoding: 7bit Content-Type: Text/Plain X-Archive-Number: 200202/145 X-Sequence-Number: 6572 That is the trivial case. The difficulty arises when the expiry plus the interval is still less than the current timestamp. In my original description of the problem this is the 'n' part of the equation. The only solution I can think of is if I can convert a date into a number of some sort and then just use normal math on it. I really need: | now - then | | ---------- | * (period+1) + then |_ period _| That's basically find the amount of time since it's expired and determine the number of periods between the expiry and now and take the floor of it. Multiply that by the number of periods plus 1 to get the new expiry. Since there is no way to divide 2 intervals in postgres I believe I need to find a way to turn a timestamp and an interval into a number. -Michael > PostgreSQL has all kinds of nifty date math tools. For example, > > > processdata=> SELECT CURRENT_TIMESTAMP AS "NOW", > CURRENT_TIMESTAMP + interval '1 hour' AS "LATER"; > > NOW | LATER > ------------------------+------------------------ > 2002-02-13 12:18:30-07 | 2002-02-13 13:18:30-07 > (1 row) > > It seems to me that what you really want isn't to add an interval > value to your expiry timestamp, but rather you need to add the > interval value to the current timestamp. The cool thing is that > intervals like '1 week', '30 days', '5 minutes' all work like you > would expect. > > So when you update your records simply do something like this: > > UPDATE my_table SET expiry = CURRENT_TIMESTAMP + interval '1 hour' > WHERE ... > > I hope this was helpful. > > Jason _________________________________________________________________ http://fastmail.ca/ - Fast Secure Web Email for Canadians From pgsql-sql-owner@postgresql.org Wed Feb 13 16:21:47 2002 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DLAaE92751 for ; Wed, 13 Feb 2002 16:10:37 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2) with HTTP id 693157; Wed, 13 Feb 2002 13:10:35 -0800 From: "Josh Berkus" Subject: Re: Time Intervals To: "Michael Richards" , jason.earl@simplot.com Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.2 Date: Wed, 13 Feb 2002 13:10:35 -0800 Message-ID: In-Reply-To: <3C6AC08C.0000A1.04458@ns.interchange.ca> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200202/146 X-Sequence-Number: 6573 Michael, > The only solution I can think of is if I can convert a date into a > number of some sort and then just use normal math on it. I really > need: > > | now - then | > | ---------- | * (period+1) + then > |_ period _| > > That's basically find the amount of time since it's expired and > determine the number of periods between the expiry and now and take > the floor of it. Multiply that by the number of periods plus 1 to get > > the new expiry. > > Since there is no way to divide 2 intervals in postgres I believe I > need to find a way to turn a timestamp and an interval into a number. Hmmm ... yeah, you're right. Do you need the date+time or just the date? If the latter, you can use the DATE data type, which is integer-based. If the former, maybe the core team needs some help implementing * and / operators for TIMESTAMP and INTERVAL ... -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco