Обсуждение: Need beginning and ending date value for a particular week in the year

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


well, a few things that come to mind.....    figure out how many days
are in the first partial week prior to week 1 of this year, that would
be something like 7 - day_of_week(YYYY-01-01), then take  '1
day'::interval * (week-1)*7 + that offset, and add that to YYYY-01-01
... add '6 day'::interval to get the last day of the week...

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!

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


well, a few things that come to mind.....    figure out how many days
are in the first partial week prior to week 1 of this year, that would
be something like 7 - day_of_week(YYYY-01-01), then take  '1
day'::interval * (week-1)*7 + that offset, and add that to YYYY-01-01
... add '6 day'::interval to get the last day of the week...

Alban Hertroys wrote:

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

There's something similar in the Wiki:

http://wiki.postgresql.org/wiki/Date_and_Time_dimensions

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.