Postgres Pro
Downloads
Home   >   mailing lists

Thread: get first / last date of given week

get first / last date of given week

From
"Lee Harr"
Date:
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

Re: get first / last date of given week

From
Jean-Luc Lachance
Date:
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
>

Re: get first / last date of given week

From
"Lee Harr"
Date:
>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, ''yyyy'');
        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