Обсуждение: BUG #7967: Wrong week number in extract function
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
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
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