Re: get first / last date of given week - Mailing list pgsql-general

Lee,

Have a look at this simpler non looping version of week_start()

-- return the first date in the given week
CREATE or REPLACE FUNCTION week_start(integer, integer)
RETURNS date
LANGUAGE 'plpgsql'
AS '
DECLARE
   pyear ALIAS FOR $1;
   pweek ALIAS FOR $2;

   year_start date;
   week_interval interval;
   week_date date;
   week_year integer;
   dow_interval interval;

BEGIN
   IF pweek < 1 THEN
     RAISE EXCEPTION ''No negative week numbers'';
   END IF;
   IF pweek > 53 THEN
     RAISE EXCEPTION ''No week numbers over 53'';
   END IF;

   year_start := to_date( pyear, 'yyyy');
   year_start_dow := date_part( 'dow', year_start);
   week_interval := pweek || '' week'';
   dow_interval := year_start_dow || '' day'';
   week_date := year_start + week_interval - dow_interval;
   week_year := extract(year FROM week_date);
   IF week_year > pyear THEN
     RAISE EXCEPTION ''No week 53 in this year'';
   END IF;

   RETURN week_date;
END;
' ;



Lee Harr wrote:

> I wrote a function to return the first date of a given week
> (and a few related functions) :
>
>
> -- return the first date in the given week
> CREATE or REPLACE FUNCTION week_start(integer, integer) RETURNS date AS '
>    DECLARE
>        pyear ALIAS FOR $1;
>        pweek ALIAS FOR $2;
>
>        year_text text;
>        year_start date;
>
>        week_text text;
>        interval_text text;
>        week_interval interval;
>        week_date date;
>        week_year integer;
>
>        day_interval interval := ''1 day'';
>
>        wk integer;
>
>    BEGIN
>        IF pweek < 1 THEN
>            RAISE EXCEPTION ''No negative week numbers'';
>        END IF;
>        IF pweek > 53 THEN
>            RAISE EXCEPTION ''No week numbers over 53'';
>        END IF;
>
>        year_text := pyear-1 || ''-12-15'';
>        year_start := year_text::date;
>
>        interval_text := pweek || '' week'';
>        week_interval := interval_text::interval;
>        week_date := year_start + week_interval;
>
>        wk := extract(week FROM week_date);
>        WHILE wk <> pweek LOOP
>            week_date := week_date + day_interval;
>            wk := extract(week FROM week_date);
>        END LOOP;
>
>        week_year := extract(year FROM week_date);
>        IF week_year > pyear THEN
>            RAISE EXCEPTION ''No week 53 in this year'';
>        END IF;
>
>        RETURN week_date;
>    END;
> ' LANGUAGE 'plpgsql';
>
>
> -- return the first date in this current week
> CREATE or REPLACE FUNCTION week_start() RETURNS date AS '
>    DECLARE
>        yr integer;
>        wk integer;
>
>    BEGIN
>        yr := extract(year from current_date);
>        wk := extract(week from current_date);
>
>        RETURN week_start(yr, wk);
>
>    END;
> ' LANGUAGE 'plpgsql';
>
>
> -- return the last date in the given year/week
> CREATE or REPLACE FUNCTION week_end(integer, integer) RETURNS date AS '
>    DECLARE
>        pyear ALIAS FOR $1;
>        pweek ALIAS FOR $2;
>
>    BEGIN
>        RETURN week_start(pyear, pweek) + interval ''6 days'';
>
>    END;
> ' LANGUAGE 'plpgsql';
>
>
> -- return the last date in the current week
> CREATE or REPLACE FUNCTION week_end() RETURNS date AS '
>    DECLARE
>        yr integer;
>        wk integer;
>
>    BEGIN
>        yr := extract(year from current_date);
>        wk := extract(week from current_date);
>
>        RETURN week_end(yr, wk);
>
>    END;
> ' LANGUAGE 'plpgsql';
>
>
>
> Have a reinvented a wheel here?  (badly? ;o)
> Is there a cookbook where I should post this code?
>
> _________________________________________________________________
> MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
> http://join.msn.com/?page=features/virus
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>