From: | Keaton Adams <kadams(at)mxlogic(dot)com> |
---|---|
To: | "\"pgsql-general(at)postgresql(dot)org\"" <pgsql-general(at)postgresql(dot)org> |
Subject: | Need beginning and ending date value for a particular week in the year |
Date: | 2009-05-26 18:03:37 |
Message-ID: | C6418B19.B988%kadams@mxlogic.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2009-05-26 18:05:33 | Re: quoting values magic |
Previous Message | ray | 2009-05-26 17:17:34 | How to initiate a new log file? |