X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A6BAD9DCA7A for ; Wed, 14 Dec 2005 23:03:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05056-07 for ; Wed, 14 Dec 2005 23:03:19 -0400 (AST) X-Greylist: delayed 01:54:37.840672 by SQLgrey- Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by postgresql.org (Postfix) with ESMTP id B55CA9DCA86 for ; Wed, 14 Dec 2005 23:03:05 -0400 (AST) Received: from smtp.kooee.com.au (santa.legion.com.au [203.17.237.157]) by svr2.postgresql.org (Postfix) with ESMTP id A550BF0AC7 for ; Thu, 15 Dec 2005 01:08:33 +0000 (GMT) Received: from [192.168.0.249] (unknown [202.125.28.143]) by smtp.kooee.com.au (Postfix) with ESMTP id A0DA91684EC; Thu, 15 Dec 2005 12:08:18 +1100 (EST) Message-ID: <43A0C200.7040103@butterflystitches.com.au> Date: Thu, 15 Dec 2005 12:08:16 +1100 From: Neil Dugan User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jaime Casanova Cc: "lucas@presserv.org" , pgsql-sql@postgresql.org Subject: Re: Extract date from week References: <20051108065256.7DFE7D7971@svr1.postgresql.org> <20051108082511.06n26rf2fc0gk8cg@www.presserv.org> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/127 X-Sequence-Number: 23669 Jaime Casanova wrote: > 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 ;) > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > Hi http://www.postgresql.org/docs/8.0/static/functions-formatting.html for the first week of 2005 use => select to_timestamp('1 2005','IW YYYY')::date as week_start; week_start ------------ 2005-01-03 Regards Neil.