Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #7967: Wrong week number in extract function

BUG #7967: Wrong week number in extract function

From
noose@noose.pl
Date:
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

Re: BUG #7967: Wrong week number in extract function

From
Thomas Kellerer
Date:
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

Re: BUG #7967: Wrong week number in extract function

From
Tom Lane
Date:
noose@noose.pl writes:
> 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)

It's correct, because "week" follows the ISO definition of week
counting.  According to that, 2012-12-31 falls in the first week of 2013.
(I have no idea how ISO arrived at their definition, but this is what it
says: weeks start on Mondays, and the first week of a year is the one
containing January 4.)

You should usually use isoyear when you are using week, so that the
results sync up.

This is all explained in
http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
although I notice that the explanation of "week" fails to show
explicitly that late-December dates can be considered to fall into the
next year.  I'll go fix that.

            regards, tom lane