Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id ECC2F2E0084 for ; Tue, 13 May 2008 05:42:54 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 10625-03 for ; Tue, 13 May 2008 05:42:48 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ipmail01.adl6.internode.on.net (ipmail01.adl6.internode.on.net [203.16.214.146]) by developer.postgresql.org (Postfix) with ESMTP id 9B9072E004B for ; Tue, 13 May 2008 05:42:50 -0300 (ADT) X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: Ah4FAK/xKEjLevdH/2dsb2JhbAAsgSeqZQ X-IronPort-AV: E=Sophos;i="4.27,478,1204464600"; d="scan'208";a="101312423" Received: from ppp247-71.static.internode.on.net (HELO DevBox.local) ([203.122.247.71]) by ipmail01.adl6.internode.on.net with ESMTP; 13 May 2008 18:12:43 +0930 Message-ID: <48295481.50800@Sheeky.Biz> Date: Tue, 13 May 2008 18:12:41 +0930 From: Shane Ambler User-Agent: Thunderbird 2.0.0.12 (Macintosh/20080213) MIME-Version: 1.0 To: Scott Marlowe CC: Achilleas Mantzios , pgsql-admin@postgresql.org, Tom Lane , Julius Tuskenis Subject: Re: how to get a number of seconds in some interval ? References: <482849B0.6050103@nsoft.lt> <200805121801.35101.achill@matrix.gatewaynet.com> <20080.1210604951@sss.pgh.pa.us> <200805130956.51872.achill@matrix.gatewaynet.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/104 X-Sequence-Number: 29188 Scott Marlowe wrote: > True. But that's only because it doesn't have a date to work against. > If you run: > > select '2007-02-01 12:00:00'::timestamp + '1 month'::interval; > you get: 2007-03-01 12:00:00 > > If you run: > select '2007-03-01 12:00:00'::timestamp + '1 month'::interval; > you get: 2007-04-01 12:00:00 > > Then, if we run: > select ('2007-03-01 12:00:00'::timestamp + '1 month'::interval) - > '2007-03-01 12:00:00'::timestamp; > we get: 31 days > > But if we run: > select ('2007-02-01 12:00:00'::timestamp + '1 month'::interval) - > '2007-02-01 12:00:00'::timestamp; > we get: 28 days > > So, I'm not sure how many days a month has. Try looking at a calendar.;-) You will find that these are the exact days between the two dates. Feb has 28 days, so 1st of feb plus 1 month puts you at 1st of march march has 31 days so 1st of march plus 1 month puts you at 1st of april. Try a leap year - select ('2008-02-01 12:00:00'::timestamp + '1 month'::interval) - '2008-02-01 12:00:00'::timestamp; ?column? ---------- 29 days (1 row) -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz