Re: Week to date function

On 25 March 2010 12:25, Ireneusz Pluta <ipluta(at)wp(dot)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(dot)ru(at)gmail(dot)com / Skype: gray-hemp / ICQ: 29353802