From: | Keaton Adams <kadams(at)mxlogic(dot)com> |
---|---|
To: | John R Pierce <pierce(at)hogranch(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Need beginning and ending date value for a particular week in the year |
Date: | 2009-05-26 18:48:46 |
Message-ID: | C64195AE.B995%kadams@mxlogic.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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" <pierce(at)hogranch(dot)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...
From | Date | Subject | |
---|---|---|---|
Next Message | Conrad Lender | 2009-05-26 19:37:37 | Re: Code tables, conditional foreign keys? |
Previous Message | Benjamin Smith | 2009-05-26 18:48:32 | Re: Code tables, conditional foreign keys? |