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: (view raw or whole 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();
  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  

Alban Hertroys

If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


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-2015 The PostgreSQL Global Development Group