X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6D399D8709 for ; Tue, 8 Nov 2005 15:01:11 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83479-10 for ; Tue, 8 Nov 2005 19:01:09 +0000 (GMT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.193]) by svr1.postgresql.org (Postfix) with ESMTP id 763DCD7CFA for ; Tue, 8 Nov 2005 15:01:08 -0400 (AST) Received: by xproxy.gmail.com with SMTP id h29so701264wxd for ; Tue, 08 Nov 2005 11:01:09 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Z+ZlMKvvHcAhXtPDAkZc6dojdzrN2kJ2tDhkmMViksDVpmRO1rQ0HWvYDRnVj8HGxHhcweQyLVxfMgw4QDfBPkZ1yANjn5MXh262PSHOdmOswo6oOI9pqjUYRmioqE4fRLU5qG7XrM5pj9xF7Em5qXNvywHO/CT5JP+hIm9Zujw= Received: by 10.64.242.2 with SMTP id p2mr6995284qbh; Tue, 08 Nov 2005 11:01:09 -0800 (PST) Received: by 10.65.180.14 with HTTP; Tue, 8 Nov 2005 11:01:09 -0800 (PST) Message-ID: Date: Tue, 8 Nov 2005 14:01:09 -0500 From: Jaime Casanova To: "lucas@presserv.org" Subject: Re: Extract date from week Cc: pgsql-sql@postgresql.org In-Reply-To: <20051108082511.06n26rf2fc0gk8cg@www.presserv.org> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <20051108065256.7DFE7D7971@svr1.postgresql.org> <20051108082511.06n26rf2fc0gk8cg@www.presserv.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.231 required=5 tests=[AWL=-0.101, RCVD_IN_BL_SPAMCOP_NET=1.332] X-Spam-Score: 1.231 X-Spam-Level: * X-Archive-Number: 200511/69 X-Sequence-Number: 23320 On 11/8/05, lucas@presserv.org wrote: > Hi > Looking the e-mail I remembered a question. > I saw that "select extract (week from now()::date)" will return the > week number > of current year. But, how can I convert a week to the first reference > date. Ex: > select extract(week from '20050105'::date); -- 5 Jan 2005 > --Returns-- > date_part | > 1 | > > It is the first week of year (2005), and how can I get what is the first = date > references the week 1? Ex: > select ???? week 1 > --should return--- > date | > 20050103 | -- 3 Jan 2005 > > Thank you. > Lucas Vendramin > > Extracted from: http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS= -DATETIME-EXTRACT --- begin extracted text --- week The number of the week of the year that the day is in. By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO-8601 week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year. (for timestamp values only) Because of this, it is possible for early January dates to be part of the 52nd or 53rd week of the previous year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005. SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 7 --- end extracted text --- -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)