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

[Lee Harr]

Have a look at this simpler non looping version of week_start()
That is a nice idea.  I had to modify it a bit in order to get
the same answers as my other function ...
CREATE or REPLACE FUNCTION week_start2(integer, integer) RETURNS date AS '
   DECLARE
   pyear ALIAS FOR $1;
   pweek ALIAS FOR $2;
   year_start date;
   year_start_dow integer;
   week_interval interval;
   week_date date;
   week_year integer;
   dow_interval interval;
   BEGIN
   IF pweek  1 THEN
   RAISE EXCEPTION ''No week numbers less than 1'';
   END IF;
   IF pweek  53 THEN
   RAISE EXCEPTION ''No week numbers over 53'';
   END IF;
   year_start := to_date(pyear, '''');
   year_start_dow := date_part(''dow'', year_start);
   week_interval := pweek-1 || '' week'';
   dow_interval := year_start_dow || '' day'';
   week_date := year_start - year_start_dow + 1 + week_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;
' LANGUAGE 'plpgsql';
_
Protect your PC - get McAfee.com VirusScan Online 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

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, '');
  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]