From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | noose(at)noose(dot)pl |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #7967: Wrong week number in extract function |
Date: | 2013-03-18 17:05:46 |
Message-ID: | 9604.1363626346@sss.pgh.pa.us |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
noose(at)noose(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | yjxiao | 2013-03-18 22:34:34 | BUG #7969: Postgres Recovery Fatal With: "incorrect local pin count: 2" |
Previous Message | Thomas Kellerer | 2013-03-18 16:55:04 | Re: BUG #7967: Wrong week number in extract function |