Message-ID: <4BAC9107.5070701@wp.pl>
Date: Fri, 26 Mar 2010 11:48:39 +0100
From: Ireneusz Pluta <ipluta@wp.pl>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: Sergey Konoplev <gray.ru@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Week to date function
References: <4BAB2C15.8090406@wp.pl>
	<c3a7de1f1003250244p440b8244t57319b742fa680f0@mail.gmail.com>
In-Reply-To: <c3a7de1f1003250244p440b8244t57319b742fa680f0@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Sergey Konoplev pisze:
> On 25 March 2010 12:25, Ireneusz Pluta <ipluta@wp.pl> wrote:
>   
>> Hello,
>>
>> 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?
>> 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.
>>     
>
> 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;
>
>   
Yes, much smarter.
However, would give the same results on (year=2009, week=53) and 
(year=2010, week=1). In fact, 2009 did not have week 53.
I wrapped it into a function with additional isoyear check and now seems OK.

Thanks


CREATE OR REPLACE FUNCTION your_week2date(double precision, double 
precision) RETURNS SETOF date
AS
$_$
SELECT  day
FROM (
    SELECT  (
                date_trunc('week', ($1::text||'-01-01')::date)
                + ($2::text||' week')::interval
                + ( d::text||'  day')::interval
            )::date  AS  day
    FROM    generate_series(0, 6) AS d
)   alias
WHERE   to_char(day, 'IYYY')::integer = $1
ORDER
BY      1
$_$
LANGUAGE SQL
IMMUTABLE
;
SELECT week2date1(date_part('year', now()), date_part('week', now()));
 week2date1
------------
 2010-03-22
 2010-03-23
 2010-03-24
 2010-03-25
 2010-03-26
 2010-03-27
 2010-03-28
(7 rows)

SELECT your_week2date(2009, 52) ;
 your_week2date
----------------
 2009-12-28
 2009-12-29
 2009-12-30
 2009-12-31
 2010-01-01
 2010-01-02
 2010-01-03
(7 rows)

SELECT your_week2date(2009, 53) ;
 your_week2date
----------------
(0 rows)

SELECT your_week2date(2010,  1) ;
 your_week2date
----------------
 2010-01-04
 2010-01-05
 2010-01-06
 2010-01-07
 2010-01-08
 2010-01-09
 2010-01-10
(7 rows)