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

On May 26, 2009, at 8:03 PM, Keaton Adams wrote:

> 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.


What I ended up doing was generating a table with:
    week    int
    year    int
    start    date
    end    date

The beauty is that you can constrain your data with foreign keys to
that table. I'm not sure that would work for partitioning though (I
think that's where you're heading?), now that I think of it.
Nevertheless, you could copy that data into your table and put a check
constraint on that instead.

I recall using generate_series() and EXTRACT(week FROM ...) to
populate the table in one pass for several years, but I don't have the
exact incantation at hand now. I'd have to experiment a bit to get
that back again, I don't have access to it anymore.

You may like to know that Postgres 8.3 has native support for ISO8601
week calculations (http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
). Maybe you should upgrade.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a1c33e310093700910733!