This looks great and is a much easier solution to the problem than what I had
planned.
Thanks!
Keaton
mydb=# select ( date('2009-01-01') + ('1 day'::interval * (21-1)*7)) - ('1
day'::interval * (select 7 - extract(dow from timestamp '2009-01-01')));
?column?
---------------------
2009-05-18 00:00:00
(1 row)
mydb=# select ( date('2009-01-01') + ('1 day'::interval * (21-1)*7)) - ('1
day'::interval * (select 7 - extract(dow from timestamp '2009-01-01'))) + '6
days'::interval;
?column?
---------------------
2009-05-24 00:00:00
(1 row)
mydb=# select ( date('2009-01-01') + ('1 day'::interval * (22-1)*7)) - ('1
day'::interval * (select 7 - extract(dow from timestamp '2009-01-01')));
?column?
---------------------
2009-05-25 00:00:00
(1 row)
mydb=# select ( date('2009-01-01') + ('1 day'::interval * (22-1)*7)) - ('1
day'::interval * (select 7 - extract(dow from timestamp '2009-01-01'))) + '6
days'::interval;
?column?
---------------------
2009-05-31 00:00:00
(1 row)
mydb=# select ( date('2009-01-01') + ('1 day'::interval * (1-1)*7)) - ('1
day'::interval * (select 7 - extract(dow from timestamp '2009-01-01')));
?column?
---------------------
2008-12-29 00:00:00
(1 row)
mydb=# select ( date('2009-01-01') + ('1 day'::interval * (1-1)*7)) - ('1
day'::interval * (select 7 - extract(dow from timestamp '2009-01-01'))) + '6
days'::interval;
?column?
---------------------
2009-01-04 00:00:00
(1 row)
On 5/26/09 12:18 PM, "John R Pierce" <pie...@hogranch.com> wrote:
> 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...
