"Michael Richards" <michael@fastmail.ca> writes:
> 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 _|
Are you *sure* that's what you want? You can certainly do it that way
--- extract(epoch from timestamp), do math, convert back --- but the
above only works if the "period" is a constant number of seconds.
Intervals like "1 month" cannot be handled as above. Less obviously,
intervals like "1 day" cannot be handled that way either (think about
daylight savings transitions).
There was a thread on this same topic just recently, and I think the
conclusion was that the cleanest way to handle real-world interval
definitions is to rely on a loop around a timestamp + interval addition
operator:
while tstamp < now do tstamp := tstamp + interval;
This is trivial to program in a plpgsql function, for example, and it's
quite cheap as long as you don't let too many periods elapse between
updates.
regards, tom lane
