Skip site navigation (1) Skip section navigation (2)

Re: How can I get the first and last date of a week, based on the week number and the year

From: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
To: Bruno Baguette <bruno(dot)baguette(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How can I get the first and last date of a week, based on the week number and the year
Date: 2008-02-27 09:19:00
Message-ID: 6659F222-3C80-4B25-9BDB-C3B0144373D5@solfertje.student.utwente.nl (view raw or flat)
Thread:
Lists: pgsql-general
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!



In response to

pgsql-general by date

Next:From: GordonDate: 2008-02-27 10:37:06
Subject: Re: Query meltdown: caching results
Previous:From: Dave PageDate: 2008-02-27 09:17:17
Subject: Re: UUID-OSSP for windows.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group