From: Michael Richards <michael@fastmail.ca>
To: pgsql-sql@postgresql.org
Subject: Time Intervals
Date: Wed, 13 Feb 2002 11:53:33 -0500 (EST)
Message-ID: <3C6A9A0D.000015.04483@ns.interchange.ca> (raw)
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 <pgsql-sql@postgresql.org>; 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" <reedstrm@rice.edu>
To: Michael Richards <michael@fastmail.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Time Intervals
Message-ID: <20020213182241.GB10858@rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
Michael Richards <michael@fastmail.ca>, 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: michael@fastmail.ca
Subject: Re: Time Intervals
In-Reply-To: <3C6A9A0D.000015.04483@ns.interchange.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox