Received: from charon.simplot.com (jrsmailhub.simplot.com [12.18.144.212]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DJOhE58386 for ; Wed, 13 Feb 2002 14:24:45 -0500 (EST) (envelope-from earlj@xanadu.simplot.com) Received: from xanadu.simplot.COM (151.113.0.132) by charon.simplot.com (MX V5.1-A AnGp) with SMTP; Wed, 13 Feb 2002 12:23:27 -0700 Received: from npa01zz001.simplot.com by xanadu.simplot.COM; (5.65v4.0/1.1.8.2/20Aug97-1116AM) id AA19436; Wed, 13 Feb 2002 12:23:27 -0700 Received: from earlj by npa01zz001.simplot.com with local (Exim 3.34 #1 (Debian)) id 16b502-0006Fq-00; Wed, 13 Feb 2002 12:23:26 -0700 To: "Michael Richards" CC: pgsql-sql@postgresql.org Subject: Re: Time Intervals References: <3C6A9A0D.000015.04483@ns.interchange.ca> From: Jason Earl Date: 13 Feb 2002 12:23:26 -0700 In-Reply-To: <3C6A9A0D.000015.04483@ns.interchange.ca> Message-ID: <874rklnq35.fsf@npa01zz001.simplot.com> Lines: 54 User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/21.1 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200202/144 X-Sequence-Number: 6571 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" 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