X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id EB16ED1DACE for ; Mon, 22 Mar 2004 12:47:49 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 76276-07 for ; Mon, 22 Mar 2004 08:47:57 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id E3B81D1C4EB for ; Mon, 22 Mar 2004 08:47:47 -0400 (AST) Received: (qmail 1883 invoked by uid 500); 22 Mar 2004 12:50:54 -0000 Date: Mon, 22 Mar 2004 06:50:54 -0600 From: Bruno Wolff III To: Martin Marques Cc: Tom Lane , Robert Creager , pgsql-sql@postgresql.org Subject: Re: inverse of "day of year" Message-ID: <20040322125054.GA1135@wolff.to> Mail-Followup-To: Martin Marques , Tom Lane , Robert Creager , pgsql-sql@postgresql.org References: <200403190906.17641.martin@bugs.unl.edu.ar> <20040319064714.405b304e.Robert_Creager@LogicalChaos.org> <24189.1079709005@sss.pgh.pa.us> <200403220832.32968.martin@bugs.unl.edu.ar> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200403220832.32968.martin@bugs.unl.edu.ar> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/215 X-Sequence-Number: 17314 On Mon, Mar 22, 2004 at 08:32:32 -0300, Martin Marques wrote: > > Is there information on how other intervals are taken? I mean, how is '1 year' > afected with the leap years? Intervals are stored as two components. One is absolute time difference, and the other is in months. '1 year' is equivalent to '12 months'. The documentation on how they work in corner cases (when added or subtracted from timestamp(tz)) is sparse. It isn't documented whether the part in months or the absolute time is added first or what timezone is used (for timestamptz) when adding the months part. The basic idea is that months are added by looking at the timestamp as date and time and adding the appropiate number of months to the date and then converting back to a timestamp. It isn't documented what happens when the day of the month is past the end of the new month, but it looks like the last day of new month is used. If you convert an interval to an absolute time (such as by extracting the epoch), then months are converted to 30 days. Again, I don't think this is documented.