Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C0C70632817 for ; Thu, 25 Mar 2010 06:45:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 66744-04 for ; Thu, 25 Mar 2010 09:44:59 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-wy0-f174.google.com (mail-wy0-f174.google.com [74.125.82.174]) by mail.postgresql.org (Postfix) with ESMTP id 17773632815 for ; Thu, 25 Mar 2010 06:44:59 -0300 (ADT) Received: by wyb38 with SMTP id 38so3377465wyb.19 for ; Thu, 25 Mar 2010 02:44:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type :content-transfer-encoding; bh=SVtwIgSUjolleIceAniMAT1gi6sIDUo+SuxyVbcjKHM=; b=Tiwsj1aFqbkQ+xioaUsZQvpB/5UeBs1SWafjpvQdKy2IXgAtszjWXYue/aO8e+QMEn U245ek+kQW1G7TuHdC9oXamlj7li+B0AXqbRZh/0zXpkONxrjBqOxIkidHHLcKKJ3CeT c5cENRm9D16waLXQVQZaiReOj6VWhVR2418gM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; b=feyH5N5W4EEnLr3b4eZE7ww0MmXHMFSXcANWNmDcO9STDZzMHg6ntT84D++P1nhkwY ozuKO0AlS8TGTwMEBDgwnR+EE1SxIdGHSaFTStKJdqizi7pEr5J9Ef23HHWrGfgg9G9H OKIpjUHB7zPthkQwI0lFjOfFGI9XbsmyzmpTo= MIME-Version: 1.0 Received: by 10.216.89.193 with SMTP id c43mr5542041wef.221.1269510298177; Thu, 25 Mar 2010 02:44:58 -0700 (PDT) In-Reply-To: <4BAB2C15.8090406@wp.pl> References: <4BAB2C15.8090406@wp.pl> From: Sergey Konoplev Date: Thu, 25 Mar 2010 12:44:38 +0300 Message-ID: Subject: Re: Week to date function To: Ireneusz Pluta Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 201003/183 X-Sequence-Number: 33890 On 25 March 2010 12:25, Ireneusz Pluta wrote: > Hello, > > is there any standard function, or a concise solution based on set of the= m, > returning a set of dates included in a week of given year and week number= ? > I ended up with creating my own function as in the example below, but I a= m > curious if I am not opening an open door. Try to think of something like this? SELECT date_trunc('week', '2010-01-01'::date) + '12 week'::interval + (d::text||' day')::interval FROM generate_series(0, 6) AS d; > > Thanks > > Irek. > > CREATE OR REPLACE FUNCTION week2date(double precision, double precision) > RETURNS SETOF date > AS > $_$ > SELECT=A0 day > FROM=A0=A0=A0 ( > =A0=A0=A0 SELECT=A0 to_char(day, 'IYYY')::integer=A0=A0=A0 AS=A0 iyyy, > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 to_char(day, 'IW'=A0 )::integer=A0=A0= =A0 AS=A0 iw, > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 day > =A0=A0=A0 FROM=A0=A0=A0 ( > =A0=A0=A0=A0=A0=A0=A0 SELECT=A0 start + generate_series(0, n)=A0=A0 AS=A0= day > =A0=A0=A0=A0=A0=A0=A0 FROM=A0=A0=A0 ( > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 SELECT=A0 start, > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (stop - start):= :integer AS=A0 n > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 FROM=A0=A0=A0 ( > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 SELECT=A0 (to_date($1::text= , 'YYYY'::text) - interval > '3 days')::date AS=A0=A0 start, > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (to= _date($1::text, 'YYYY'::text) + interval '1 year > 3 days')::date AS=A0=A0 stop > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 )=A0=A0=A0=A0=A0=A0 ss > =A0=A0=A0=A0=A0=A0=A0 )=A0=A0=A0=A0=A0=A0 aa > =A0=A0=A0 )=A0=A0=A0=A0=A0=A0 bb > )=A0=A0=A0=A0=A0=A0 cc > WHERE=A0=A0 iw =3D $2 AND iyyy =3D $1 > ORDER > BY=A0=A0=A0=A0=A0 day > $_$ > LANGUAGE SQL > IMMUTABLE > ; > > SELECT week2date(date_part('year', now()), date_part('week', now())); > =A0week2date > ------------ > =A02010-03-22 > =A02010-03-23 > =A02010-03-24 > =A02010-03-25 > =A02010-03-26 > =A02010-03-27 > =A02010-03-28 > (7 rows) > > SELECT week2date(2009, 53); > =A0week2date > ------------ > =A02009-12-28 > =A02009-12-29 > =A02009-12-30 > =A02009-12-31 > =A02010-01-01 > =A02010-01-02 > =A02010-01-03 > (7 rows) > > SELECT week2date(2010,=A0 1); > =A0week2date > ------------ > =A02010-01-04 > =A02010-01-05 > =A02010-01-06 > =A02010-01-07 > =A02010-01-08 > =A02010-01-09 > =A02010-01-10 > (7 rows) > > --=20 Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802