Обсуждение: Week numbers and years

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

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.