Title: Need beginning and ending date value for a particular week in the year
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.
