Re: [GENERAL] get first / last date of given week

[Jean-Luc Lachance]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]