Hello !

I have a week number (ISO 8601) and a year, based on theses values, I
would like to get the first and the last dates of that week.

How I can do that ?

The only solution is doing a big generate_series to build a subset that
contains the week of all the dates between the 01/01 || year and the
31/12 || year. But I find that solution quite dirty and ressources
consumming.

Is there a cleanest way to do that ?

Many thanks in advance !

Regards,

--
Bruno Baguette - bruno.baguette@gmail.com



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tuesday 26 February 2008 5:32 pm, Bruno Baguette wrote:
> Hello !
>
> I have a week number (ISO 8601) and a year, based on theses values, I
> would like to get the first and the last dates of that week.
>
> How I can do that ?
>
> The only solution is doing a big generate_series to build a subset that
> contains the week of all the dates between the 01/01 || year and the
> 31/12 || year. But I find that solution quite dirty and ressources
> consumming.
>
> Is there a cleanest way to do that ?
>
> Many thanks in advance !
>
> Regards,

This will get you to the Monday of the week .

select to_date('9 08','IW YY');
to_date
------------
2008-02-25
(1 row)

Where 9 is the ISO week number and 08 is the year. See below for more details:
http://www.postgresql.org/docs/8.2/i...ormatting.html
--
Adrian Klaver
aklaver@comcast.net

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Bruno Baguette wrote:
> Hello !
>
> I have a week number (ISO 8601) and a year, based on theses values, I
> would like to get the first and the last dates of that week.
>
> How I can do that ?
>
> The only solution is doing a big generate_series to build a subset that
> contains the week of all the dates between the 01/01 || year and the
> 31/12 || year. But I find that solution quite dirty and ressources
> consumming.
>
> Is there a cleanest way to do that ?
>

Use pl/Perl and the Date::Calc module.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Adrian Klaver <aklaver@comcast.net> writes:
> On Tuesday 26 February 2008 5:32 pm, Bruno Baguette wrote:
>> I have a week number (ISO 8601) and a year, based on theses values, I
>> would like to get the first and the last dates of that week.

> This will get you to the Monday of the week .

> select to_date('9 08','IW YY');
> to_date
> ------------
> 2008-02-25
> (1 row)

date_trunc('week', ...) would probably be a more convenient way of doing
that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Tuesday 26 February 2008 8:39 pm, Tom Lane wrote:
> Adrian Klaver <aklaver@comcast.net> writes:
> > On Tuesday 26 February 2008 5:32 pm, Bruno Baguette wrote:
> >> I have a week number (ISO 8601) and a year, based on theses values, I
> >> would like to get the first and the last dates of that week.
> >
> > This will get you to the Monday of the week .
> >
> > select to_date('9 08','IW YY');
> > to_date
> > ------------
> > 2008-02-25
> > (1 row)
>
> date_trunc('week', ...) would probably be a more convenient way of doing
> that.
>
> regards, tom lane

I am not sure I follow. The OP has a ISO week number and a year and wants
dates. I thought date_trunc('field',source) requires a timestamp or interval
as its source.
--
Adrian Klaver
aklaver@comcast.net

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Adrian Klaver <aklaver@comcast.net> writes:
> On Tuesday 26 February 2008 8:39 pm, Tom Lane wrote:
>> date_trunc('week', ...) would probably be a more convenient way of doing
>> that.

> I am not sure I follow. The OP has a ISO week number and a year and wants
> dates. I thought date_trunc('field',source) requires a timestamp or interval
> as its source.

Oh, you are right ... I misread the question :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Bruno Baguette wrote:
> Hello !
>
> I have a week number (ISO 8601) and a year, based on theses values, I
> would like to get the first and the last dates of that week.

Broken down step-by-step. End of week left as an exercise

SELECT
doy,
EXTRACT(dow FROM doy) AS offset,
(doy - EXTRACT(dow FROM doy) * '1 day'::interval)::date AS start_of_week
FROM
(SELECT ('2008-01-04'::date + 8 * '1 week'::interval)::date AS doy)
AS foo;

doy | offset | start_of_week
------------+--------+---------------
2008-02-29 | 5 | 2008-02-24
(1 row)

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Feb 27, 2008, at 2:32 AM, Bruno Baguette wrote:

> Hello !
>
> I have a week number (ISO 8601) and a year, based on theses values,
> I would like to get the first and the last dates of that week.
>
> How I can do that ?
>
> The only solution is doing a big generate_series to build a subset
> that contains the week of all the dates between the 01/01 || year
> and the 31/12 || year. But I find that solution quite dirty and
> ressources consumming.
>
> Is there a cleanest way to do that ?

You can use to_date for most of that, like:
development=> select to_date('01 02 2008', 'ID IW YYYY') AS start,
to_date('07 02 2008', 'ID IW YYYY') AS end;
start | end
------------+------------
2008-01-07 | 2008-01-07
(1 row)

I'm a bit surprised that specifying the weekdays doesn't make any
difference here, maybe it's my version?:

development=> select version();
version
------------------------------------------------------------------------
------------------------
PostgreSQL 8.2.4 on i386-portbld-freebsd6.2, compiled by GCC cc
(GCC) 3.4.6 [FreeBSD] 20060305
(1 row)


Anyway, you can solve that by adding an interval '6 days' to the end
result.

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,47c525af233091991417831!



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq