A minor feature request:

PostgreSQL supports ISO-8601 week numbers with the syntax EXTRACT(WEEK FROM
timestamp) or TO_CHAR(timestamp,'IW'). There is, however, no easy way to
extract the year corresponding to the week number.

Since ISO weeks may overlap year boundaries, this makes the week number
functionality nearly unusable in aggregate queries. (e.g. both EXTRACT(WEEK
FROM timestamp '2002-01-01') and EXTRACT(WEEK FROM timestamp '2002-12-31')
returns 1.)

A way to extract the year-of-week would make the extract(week from
date)-functionality a lot more useful.

-- 
Brage Fï¿½rland


Wed, 25 Jun 2003 14:05:10 +0200
Brage <brage@zoo.uib.no>
Week numbers and years
On Wed, Jun 25, 2003 at 14:05:10 +0200,
  Brage <brage@zoo.uib.no> wrote:
> A minor feature request:
> 
> PostgreSQL supports ISO-8601 week numbers with the syntax EXTRACT(WEEK FROM
> timestamp) or TO_CHAR(timestamp,'IW'). There is, however, no easy way to
> extract the year corresponding to the week number.
> 
> Since ISO weeks may overlap year boundaries, this makes the week number
> functionality nearly unusable in aggregate queries. (e.g. both EXTRACT(WEEK
> FROM timestamp '2002-01-01') and EXTRACT(WEEK FROM timestamp '2002-12-31')
> returns 1.)
> 
> A way to extract the year-of-week would make the extract(week from
> date)-functionality a lot more useful.

While it would probably be nice to have a simple way to do it, you
should still be able to get the year of the week. Using the day of
the week and the date of the day of interest, you should be able to
get the date of the Thursday in the week and the year of that date
is the year of the week.

Sun, 29 Jun 2003 10:34:52 -0500
Bruno Wolff III <bruno@wolff.to>
Re: Week numbers and years