Re: BUG #1217: wrong date->number of week conversion

The following bug has been logged online:

Bug reference: 1217
Logged by: Romuald Pilitowski

Email address: rpilitow(at)galera(dot)ii(dot)pw(dot)edu(dot)pl

PostgreSQL version: 7.4

Operating system: Linux Mandrake 10.0

Description: wrong date->number of week conversion

Details:

hi,
I encountered following problem - executing command:
select to_char(to_date('2003-12-29','YYYY-MM-DD'),'YYYY-IW-DD')
I receive
2003-01-29
(!!)

Frankly speaking I don`t know the adequate calendar ISO norm in detail, but
the last days of year shouldn`t be counted as F_I_R_S_T week of that year. I
expected PostgreSQL rather to return 2003-53-29.

Some extra details about my OS:

uname -a
Linux computer 2.6.3-4mdk #1 Tue Mar 2 07:26:13 CET 2004 i586 unknown
unknown GNU/Linux

SELECT version()
PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
20030222 (Red Hat Linux 3.2.2-5

Romuald Pilitowski, Poland

"PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> writes:
> Frankly speaking I don`t know the adequate calendar ISO norm in detail, but
> the last days of year shouldn`t be counted as F_I_R_S_T week of that year. I
> expected PostgreSQL rather to return 2003-53-29.

You are mistaken.

The ISO week is defined as Mon-Sun where week 1 of a year is the week
containing the year's first Thursday. Don't ask me *why* it's defined
that way, but it is. 2004's first Thursday is Jan 1, so that week
extends from 2003-12-29 to 2004-01-04.

regards, tom lane

This look like it would be nice to have it configurable.

first_day_of_the_week
first_day_of the_year

Tom Lane wrote:

> "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> writes:
>
>>Frankly speaking I don`t know the adequate calendar ISO norm in detail, but
>>the last days of year shouldn`t be counted as F_I_R_S_T week of that year. I
>>expected PostgreSQL rather to return 2003-53-29.
>
>
> You are mistaken.
>
> The ISO week is defined as Mon-Sun where week 1 of a year is the week
> containing the year's first Thursday. Don't ask me *why* it's defined
> that way, but it is. 2004's first Thursday is Jan 1, so that week
> extends from 2003-12-29 to 2004-01-04.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

Jean-Luc Lachance wrote:
> This look like it would be nice to have it configurable.
>
> first_day_of_the_week
> first_day_of the_year

Well, this isn't really negotiable. You can define your own calendar
functions that do this, but the standard ones need to be left alone.
This standard isn't just theoretical, btw. Over here, many calendars
(pocket, wall) have the week number printed on it, and people say
things like, "we will take care of that in week 37".

--
Peter Eisentraut
http://developer.postgresql.org/~petere/