Re: Time Intervals

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

"Michael Richards" <michael@fastmail.ca> writes:

> 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
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html