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