Received: from mail.interchange.ca (ns.interchange.ca [216.126.79.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DGubE03748 for ; Wed, 13 Feb 2002 11:58:00 -0500 (EST) (envelope-from michael@fastmail.ca) Received: by mail.interchange.ca (Fastmailer, from userid 555) id DBDAC2BFD; Wed, 13 Feb 2002 11:53:33 -0500 (EST) MIME-Version: 1.0 Message-Id: <3C6A9A0D.000015.04483@ns.interchange.ca> To: pgsql-sql@postgresql.org Subject: Time Intervals 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 16:53:33 +0000 (UTC) Date: Wed, 13 Feb 2002 11:53:33 -0500 (EST) Content-Transfer-Encoding: 7bit Content-Type: Text/Plain X-Archive-Number: 200202/140 X-Sequence-Number: 6568 I've got a rather odd problem that I can't seem to solve easily with the given date manipulation functions. I've got an expiry timestamp and a renewal interval. If something has expired it gets renewed as the expiry + renewal * n Where n is the smallest number that will cause the calculation to result in the future. So if I've got a resource that is renewed by the hour and it expired last week then I need to add on enough hours so its new expiry will be up to 1 hour in the future. Only trouble is this renewal period can be anything from minutes to months and it may have expired up to 6 months ago. If I could convert the timestamp into a julian of some sort perhaps I could do the math that way. Any ideas? -Michael _________________________________________________________________ http://fastmail.ca/ - Fast Secure Web Email for Canadians From pgsql-sql-owner@postgresql.org Wed Feb 13 13:43:42 2002 Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DIMgE40345 for ; Wed, 13 Feb 2002 13:22:42 -0500 (EST) (envelope-from reedstrm@rice.edu) Received: from localhost (localhost [127.0.0.1]) by ece.rice.edu (Postfix) with ESMTP id 3412D68A66; Wed, 13 Feb 2002 12:22:42 -0600 (CST) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (Postfix) with ESMTP id 672FC68A61; Wed, 13 Feb 2002 12:22:41 -0600 (CST) Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.34 #1 (Debian)) id 16b43F-0002rr-00; Wed, 13 Feb 2002 12:22:41 -0600 Date: Wed, 13 Feb 2002 12:22:41 -0600 From: "Ross J. Reedstrom" To: Michael Richards Cc: pgsql-sql@postgresql.org Subject: Re: Time Intervals Message-ID: <20020213182241.GB10858@rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , Michael Richards , pgsql-sql@postgresql.org References: <3C6A9A0D.000015.04483@ns.interchange.ca> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3C6A9A0D.000015.04483@ns.interchange.ca> User-Agent: Mutt/1.3.27i X-Virus-Scanned: by AMaViS snapshot-20010714 X-Archive-Number: 200202/141 X-Sequence-Number: 6569 On Wed, Feb 13, 2002 at 11:53:33AM -0500, Michael Richards wrote: > I've got a rather odd problem that I can't seem to solve easily with > the given date manipulation functions. > > I've got an expiry timestamp and a renewal interval. If something has > expired it gets renewed as the expiry + renewal * n > Where n is the smallest number that will cause the calculation to > result in the future. > > So if I've got a resource that is renewed by the hour and it expired > last week then I need to add on enough hours so its new expiry will > be up to 1 hour in the future. Only trouble is this renewal period > can be anything from minutes to months and it may have expired up to > 6 months ago. > > If I could convert the timestamp into a julian of some sort perhaps I > could do the math that way. > > Any ideas? Hmm, If I undestand your problem correctly, it's actually pretty easy: you just need to see if expiry is in the past, and if it is, set it to current_timestamp + renewal_interval. If your doing the license expired detection in the frontend in a procedural language, just do a simple update. If you want to hide all that in the backend, you _still_ probably need to use a procedural language, such as pgpsql. Do you want to actually update the databse table with a new expiry, or just calculate one on the fly? Ross