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 25CAED1BC62 for ; Mon, 22 Mar 2004 13:16:48 +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 86136-06 for ; Mon, 22 Mar 2004 09:16:55 -0400 (AST) Received: from bugs.unl.edu.ar (bugs.unl.edu.ar [168.96.132.208]) by svr1.postgresql.org (Postfix) with ESMTP id 08D05D1B55C for ; Mon, 22 Mar 2004 09:16:46 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by bugs.unl.edu.ar (Postfix) with ESMTP id E7D8411E3A; Mon, 22 Mar 2004 10:14:42 -0300 (ART) Received: from bugs.unl.edu.ar ([127.0.0.1]) by localhost (bugs [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08402-05; Mon, 22 Mar 2004 10:14:42 -0300 (ART) Received: from localhost (localhost [127.0.0.1]) by bugs.unl.edu.ar (Postfix) with ESMTP id EF96311E39; Mon, 22 Mar 2004 10:14:41 -0300 (ART) From: Martin Marques To: Bruno Wolff III Subject: Re: inverse of "day of year" Date: Mon, 22 Mar 2004 10:14:40 -0300 User-Agent: KMail/1.5.4 References: <200403190906.17641.martin@bugs.unl.edu.ar> <200403220832.32968.martin@bugs.unl.edu.ar> <20040322125054.GA1135@wolff.to> In-Reply-To: <20040322125054.GA1135@wolff.to> Cc: Tom Lane , Robert Creager , pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200403221014.40555.martin@bugs.unl.edu.ar> X-Virus-Scanned: by amavisd-new-20030616-p7 (Debian) at unl.edu.ar X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/217 X-Sequence-Number: 17316 El Lun 22 Mar 2004 09:50, escribi=F3: > > 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 us= ed > (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. Any thoughts on how this could affect date manipulation? mydb=3D> select '29/2/2004'::date + ((2005 - date_part('year', now())::int)= ||=20 'years')::interval; ?column? --------------------- 2005-02-28 00:00:00 AFAIKS with other dates this works OK. :-) --=20 10:11:02 up 13 days, 14:42, 4 users, load average: 0.17, 0.12, 0.16 ----------------------------------------------------------------- Mart=EDn Marqu=E9s | select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral -----------------------------------------------------------------