PG 8.1.17
For a given week number (2009w22) I need to calculate the beginning and ending
date that makes up that particular week in the year. I want to use the
beginning/ending date as part of a CHECK constraint on an inherited table, with
each child table based on a week of the year.
This is a function in the system someone wrote to get the current week of the
year, with our week starting on a Monday, not Sunday:
CREATE OR REPLACE FUNCTION get_weeknum (TIMESTAMP WITH TIME ZONE) RETURNS
VARCHAR AS $$
DECLARE
tstamp ALIAS FOR $1;
dow INTEGER;
BEGIN
-- to_char(2005-01-01, YYYY"w"IW), for example, returns 2005w53 but we need
2004w53 so
-- we return YYYY"w"IW for the thursday of the week of the specified date
SELECT INTO dow to_char(tstamp, 'D');
-- "D" returns sun=1 - sat=7 but we need dow to be mon=1 - sun=7
IF dow = 1 THEN
dow = 7;
ELSE
dow = dow - 1;
END IF;
RETURN to_char(tstamp - interval '1 Day' * (dow - 4), 'YYYY"w"IW');
END;
$$ LANGUAGE 'plpgsql';
So for the calculated week value (i.e. 2009w22) I need to be able to calculate
the first and last day of the week (05/25/2009 and 05/31/2009). Is there a
clean / fairly easy way to do this? I can think of doing some string
comparisons and walking through date values to figure it out but was wondering
if there was a rather simplistic way to do this that I am overlooking.
