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: Richard Huxton <dev(at)archonet(dot)com>
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:13:14
Message-ID: 47C529AA.8080505@archonet.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Magnus HaganderDate: 2008-02-27 09:14:39
Subject: Re: citext in windows.
Previous:From: Ivan Sergio BorgonovoDate: 2008-02-27 09:11:13
Subject: dump/restore on different locale

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