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
>From pgsql-sql-owner(at)postgresql(dot)org Wed Feb 13 16:21:47 2002
Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250])
by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DLAaE92751
for <pgsql-sql(at)postgresql(dot)org>; Wed, 13 Feb 2002 16:10:37 -0500 (EST)
(envelope-from josh(at)agliodbs(dot)com)
Received: from [209.10.40.250] (account <josh(at)agliodbs(dot)com>)
by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2)
with HTTP id 693157; Wed, 13 Feb 2002 13:10:35 -0800
From: "Josh Berkus" <josh(at)agliodbs(dot)com>
Subject: Re: Time Intervals
To: "Michael Richards" <michael(at)fastmail(dot)ca>, jason(dot)earl(at)simplot(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
X-Mailer: CommuniGate Pro Web Mailer v.3.5.2
Date: Wed, 13 Feb 2002 13:10:35 -0800
Message-ID: <web-693157(at)davinci(dot)ethosmedia(dot)com>
In-Reply-To: <3C6AC08C(dot)0000A1(dot)04458(at)ns(dot)interchange(dot)ca>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit
X-Archive-Number: 200202/146
X-Sequence-Number: 6573

Michael,

> 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.

Hmmm ... yeah, you're right. Do you need the date+time or just the
date? If the latter, you can use the DATE data type, which is
integer-based.

If the former, maybe the core team needs some help implementing * and /
operators for TIMESTAMP and INTERVAL ...

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco