Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DLpVE06221 for ; Wed, 13 Feb 2002 16:51:32 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g1DLouf00886; Wed, 13 Feb 2002 16:50:56 -0500 (EST) To: "Michael Richards" cc: jason.earl@simplot.com, pgsql-sql@postgresql.org Subject: Re: Time Intervals In-reply-to: <3C6AC08C.0000A1.04458@ns.interchange.ca> References: <3C6AC08C.0000A1.04458@ns.interchange.ca> Comments: In-reply-to "Michael Richards" message dated "Wed, 13 Feb 2002 14:37:48 -0500" Date: Wed, 13 Feb 2002 16:50:55 -0500 Message-ID: <883.1013637055@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200202/148 X-Sequence-Number: 6575 "Michael Richards" 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