Need beginning and ending date value for a particular week in the year

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.

Responses

Browse pgsql-general by date

  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?