Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id D0D0163240A
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Mar 2010 06:26:50 -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 64456-05-2 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Mar 2010 09:26:40 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mx4.wp.pl (mx4.wp.pl [212.77.101.8])
	by mail.postgresql.org (Postfix) with ESMTP id 6E973633837
	for <pgsql-sql@postgresql.org>; Thu, 25 Mar 2010 06:25:45 -0300 (ADT)
Received: (wp-smtpd smtp.wp.pl 7508 invoked from network);
	25 Mar 2010 10:25:43 +0100
Received: from abbl251.neoplus.adsl.tpnet.pl (HELO [127.0.0.1])
	(ipluta@[83.6.201.251]) (envelope-sender <ipluta@wp.pl>)
	by smtp.wp.pl (WP-SMTPD) with AES256-SHA encrypted SMTP
	for <pgsql-sql@postgresql.org>; 25 Mar 2010 10:25:43 +0100
Message-ID: <4BAB2C15.8090406@wp.pl>
Date: Thu, 25 Mar 2010 10:25:41 +0100
From: Ireneusz Pluta <ipluta@wp.pl>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Week to date function
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-WP-AV: skaner antywirusowy poczty Wirtualnej Polski S. A.
X-WP-SPAM: NO 0000000 [MaMk]                               
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.459 tagged_above=-10 required=5
	tests=BAYES_50=0.001, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.457
X-Spam-Level: *
X-Archive-Number: 201003/182
X-Sequence-Number: 33889

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
<font face="Courier New, Courier, monospace">Hello,<br>
<br>
is there any standard function, or a concise solution based on set of
them, returning a set of dates included in a week of given year and
week number? <br>
I ended up with creating my own function as in the example below, but I
am curious if I am not opening an open door. <br>
<br>
Thanks<br>
<br>
Irek.<br>
<br>
CREATE OR REPLACE FUNCTION week2date(double precision, double
precision) RETURNS SETOF date<br>
AS<br>
$_$<br>
SELECT  day<br>
FROM    (<br>
    SELECT  to_char(day, 'IYYY')::integer    AS  iyyy,<br>
            to_char(day, 'IW'  )::integer    AS  iw,<br>
            day<br>
    FROM    (<br>
        SELECT  start + generate_series(0, n)   AS  day<br>
        FROM    (<br>
            SELECT  start,<br>
                    (stop - start)::integer AS  n<br>
            FROM    (<br>
                SELECT  (to_date($1::text, 'YYYY'::text) -
interval        '3 days')::date AS   start,<br>
                        (to_date($1::text, 'YYYY'::text) + interval '1
year 3 days')::date AS   stop<br>
            )       ss<br>
        )       aa<br>
    )       bb<br>
)       cc<br>
WHERE   iw = $2 AND iyyy = $1<br>
ORDER<br>
BY      day<br>
$_$<br>
LANGUAGE SQL<br>
IMMUTABLE<br>
;<br>
<br>
SELECT week2date(date_part('year', now()), date_part('week', now()));<br>
 week2date<br>
------------<br>
 2010-03-22<br>
 2010-03-23<br>
 2010-03-24<br>
 2010-03-25<br>
 2010-03-26<br>
 2010-03-27<br>
 2010-03-28<br>
(7 rows)<br>
<br>
SELECT week2date(2009, 53);<br>
 week2date<br>
------------<br>
 2009-12-28<br>
 2009-12-29<br>
 2009-12-30<br>
 2009-12-31<br>
 2010-01-01<br>
 2010-01-02<br>
 2010-01-03<br>
(7 rows)<br>
<br>
SELECT week2date(2010,  1);<br>
 week2date<br>
------------<br>
 2010-01-04<br>
 2010-01-05<br>
 2010-01-06<br>
 2010-01-07<br>
 2010-01-08<br>
 2010-01-09<br>
 2010-01-10<br>
(7 rows)<br>
<br>
</font>
</body>
</html>