Re: BUG #7967: Wrong week number in extract function - Mailing list pgsql-bugs

noose@noose.pl wrote on 18.03.2013 09:23:
> The following bug has been logged on the website:
>
> Bug reference:      7967
> Logged by:          Pawel Kobylak
> Email address:      noose@noose.pl
> PostgreSQL version: 9.1.3
> Operating system:   Debian
> Description:
>
> Hi,
> I'm running that query and result is ... unexpected for me...
>
> Query:
> select '2012-12-31', EXTRACT(year from '2012-12-31'::date), EXTRACT(week
> from '2012-12-31'::date)
>
> Result:
> "2012-12-31";2012;1
>
> Expected:
> "2012-12-31";2012;53
> OR
> "2012-12-31";2013;1
>
> This result is correct? Or that is little bug? :-)
> Regards,
> Pawel

Expected - or at least documented.

You are looking for "isoyear" instead of "year":

select extract(isoyear from date '2012-12-31')
Result: 2013

The same "option" is available for the to_char() function: IYYY vs. YYYY