pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (9+ messages)  latest in thread

Message-ID: <3C6A9A0D.000015.04483@ns.interchange.ca>
Permalink:  ../3C6A9A0D.000015.04483@ns.interchange.ca/
Also on:    postgresql.org/message-id/3C6A9A0D.000015.04483@ns.interchange.ca

 · 

reply

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