Re: Time Intervals

That is the trivial case. The difficulty arises when the expiry plus 
the interval is still less than the current timestamp. In my original 
description of the problem this is the 'n' part of the equation.

The only solution I can think of is if I can convert a date into a 
number of some sort and then just use normal math on it. I really 
need:

| now - then |
| ---------- | * (period+1) + then
|_  period  _|        
That's basically find the amount of time since it's expired and 
determine the number of periods between  the expiry and now and take 
the floor of it. Multiply that by the number of periods plus 1 to get 
the new expiry.

Since there is no way to divide 2 intervals in postgres I believe I 
need to find a way to turn a timestamp and an interval into a number.

-Michael

> 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

_________________________________________________________________   http://fastmail.ca/ - Fast Secure Web Email for
Canadians