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;
>
> Thanks
>
> Irek.
>
> CREATE OR REPLACE FUNCTION week2date(double precision, double precision)
> RETURNS SETOF date
> AS
> $_$
> SELECT day
> FROM (
> SELECT to_char(day, 'IYYY')::integer AS iyyy,
> to_char(day, 'IW' )::integer AS iw,
> day
> FROM (
> SELECT start + generate_series(0, n) AS day
> FROM (
> SELECT start,
> (stop - start)::integer AS n
> FROM (
> SELECT (to_date($1::text, 'YYYY'::text) - interval
> '3 days')::date AS start,
> (to_date($1::text, 'YYYY'::text) + interval '1 year
> 3 days')::date AS stop
> ) ss
> ) aa
> ) bb
> ) cc
> WHERE iw = $2 AND iyyy = $1
> ORDER
> BY day
> $_$
> LANGUAGE SQL
> IMMUTABLE
> ;
>
> SELECT week2date(date_part('year', now()), date_part('week', now()));
> week2date
> ------------
> 2010-03-22
> 2010-03-23
> 2010-03-24
> 2010-03-25
> 2010-03-26
> 2010-03-27
> 2010-03-28
> (7 rows)
>
> SELECT week2date(2009, 53);
> 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 week2date(2010, 1);
> week2date
> ------------
> 2010-01-04
> 2010-01-05
> 2010-01-06
> 2010-01-07
> 2010-01-08
> 2010-01-09
> 2010-01-10
> (7 rows)
>
>
--
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