Time Intervals

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(at)postgresql(dot)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 <pgsql-sql(at)postgresql(dot)org>; Wed, 13 Feb 2002 13:22:42 -0500 (EST)
(envelope-from reedstrm(at)rice(dot)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" <reedstrm(at)rice(dot)edu>
To: Michael Richards <michael(at)fastmail(dot)ca>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Time Intervals
Message-ID: <20020213182241(dot)GB10858(at)rice(dot)edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm(at)ece(dot)rice(dot)edu>,
Michael Richards <michael(at)fastmail(dot)ca>, pgsql-sql(at)postgresql(dot)org
References: <3C6A9A0D(dot)000015(dot)04483(at)ns(dot)interchange(dot)ca>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3C6A9A0D(dot)000015(dot)04483(at)ns(dot)interchange(dot)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