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-222010-03-232010-03-242010-03-252010-03-262010-03-272010-03-28
(7 rows)
SELECT your_week2date(2009, 52) ;your_week2date
----------------2009-12-282009-12-292009-12-302009-12-312010-01-012010-01-022010-01-03
(7 rows)
SELECT your_week2date(2009, 53) ;your_week2date
----------------
(0 rows)
SELECT your_week2date(2010, 1) ;your_week2date
----------------2010-01-042010-01-052010-01-062010-01-072010-01-082010-01-092010-01-10
(7 rows)