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 9440ED1E1DD for ; Fri, 19 Mar 2004 15:10:56 +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 38307-03 for ; Fri, 19 Mar 2004 11:10:52 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 7EF70D1DD0F for ; Fri, 19 Mar 2004 11:10:47 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i2JFA6xs024190; Fri, 19 Mar 2004 10:10:06 -0500 (EST) To: Robert Creager Cc: Martin Marques , pgsql-sql@postgresql.org Subject: Re: inverse of "day of year" In-reply-to: <20040319064714.405b304e.Robert_Creager@LogicalChaos.org> References: <200403190906.17641.martin@bugs.unl.edu.ar> <20040319064714.405b304e.Robert_Creager@LogicalChaos.org> Comments: In-reply-to Robert Creager message dated "Fri, 19 Mar 2004 06:47:14 -0700" Date: Fri, 19 Mar 2004 10:10:05 -0500 Message-ID: <24189.1079709005@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/204 X-Sequence-Number: 17303 Robert Creager writes: > Martin Marques confessed: >> Is there a function that would give me the date for a given day of year? > Something like: > select date_trunc( 'year', now() ) + (extract( doy from now() ) - 1) * > '1day'::interval; timestamp + interval arithmetic is likely to give you problems at daylight savings boundaries, since '1day' will be taken as '24hours'. A more reliable way to get (what I assume is) the desired result is to use the date + integer operator: select date_trunc('year', now())::date + (extract(doy from now()) - 1)::integer; regards, tom lane